How to Add or Subtract Dates in Excel

Whether you’re tracking project timelines, managing financial data, or planning events, the ability to add or subtract dates in Excel is a game-changer, and we’re here to help.

Working with dates in Microsoft Excelcan sometimes feel like navigating a labyrinth, especially when you need to add orsubtract days, months, or years. In this comprehensive guide, we’re diving into the intricacies of Excel date manipulation.

1. Add or Subtract Days to Date with a Formula

If you’re looking to add or subtract a specific number of days to a date in a cell or dates in an entire column, you’ll use a specific arithmetic operation.

There are several formulas you can use to add several days to a date in an Excel cell and automatically display the future date in a separate cell.

The simplest formula is in the form of a cell reference + number of days you want to add. Let’s say you want to add 10 days to the date in the A2 cell. Your formula will be =A2+10

Another simple formula you can use is the DATE(year, month, day) function. That formula looks like this: =DATE(2015, 9, 20)+10

And finally, the third possibility of how to add days to a date in the cell is to do it as a result of another function. You can, for example, use the TODAY() function. The formula is =TODAY()+10.

As you can see, for this particular function you don’t need to have today’s date written in the cell. An empty cell will do because Excel can recognize the current date.

Subtracting a certain number of days from a given day is similar to adding it. In fact, the formulas remain the same, but instead of +N days, you’ll use -N days. Here are the examples:

=A2-10

=DATE(2015, 9, 20)-10

=TODAY()-10

2. Add or Subtract Days to Date Without a Formula

If you don’t likeusing Excel formulas, you can use the Paste Special feature in Excel in order to add or subtract a specific number of days from multiple dates. Here’s how:

3. Add or Subtract Weeks to Date in Microsoft Excel

Adding or subtracting weeks from the dates in the Excel cells is similar to adding or subtracting days. In fact, you’ll be using the same formulas, but instead of adding days, you’ll be multiplying the number of weeks you want to add by 7.

Let’s see the formulas in the examples.

If you want to add 4 weeks your formula should look like this:

=A2+4 *7

=DATE(2015, 9, 20)+4*7

=TODAY()+4*7

Subtracting weeks is the same. Instead of +N weeks, you’ll use -N weeks.

A2-4 *7

=DATE(2015, 9, 20)-4*7

=TODAY()-4*7

4. Add or Subtract Months to Date in Microsoft Excel

There are two functions you can use to add or subtract a whole month from a date in a cell. You’ll use either the DATE or EDATE function.

You’re going to need three Excel cells to add a whole month or months to a specific date. In the first cell, let’s say A2, you have the date to which you want to add months. In C2, type the number of months you want to add (for example, 2). You’ll type the following formula in column B cell 2:

=DATE(YEAR(A2),MONTH(A2),+$c$2,DAY(A2))

Subtracting with this formula is easy. Just add -2 in the C2 cell or the appropriate part of the formula:

Or

=DATE(YEAR(A2),MONTH(A2),-$c$2,DAY(A2))

You can also input the number of months you want directly into the formula, instead of typing it in the C2 cell and referencing it.

=DATE(YEAR(A2),MONTH(A2),+2,DAY(A2))

EDATE formula will return a date that’s specified number of months before or after the start date. This formula contains two arguments. The Start Date value (the date to which you want to add months) will be the first argument. The second argument will be the number of months you want to add or subtract.

Your formula will look like this: =EDATE(start date cell reference, months to add). You can use the same format as for the DATE function.

Adding months example: =EDATE(A2,$C$2)

You can use the same formula to subtract months the same way as with the DATE function. Simply put a negative number in a C2 cell and use the same formula.

Subtracting months example: =EDATE(A2,$C$2)

5. Add or Subtract Years to Date in Microsoft Excel

You can use the DATE function in Excel to add any number of years to a given date, the same way you use it to add months. This time, you’ll have to specify how many years you want to add.

Your formula will look like this: DATE(YEAR(date) + N years, Month(date), Day(date))

Let’s see it in an example. Say you want to add two years to the date in the cell. Your formula should be as follows:

=DATE(YEAR(A2)+2,MONTH(A2),DAY(A2))

To subtract a year from a date, we can use the same formula, but instead of positive, you should use the negative value for years.

Another way to subtract a year is to type the -N of years in the separate cell, C2. In that case, you’ll use the =DATE(YEAR(A2)+$C$2,MONTH(A2),DAY(A2))

6. Add or Subtract a Combination of Years, Months, Weeks, and Days to Date in Microsoft Excel

If you want to add or subtract a combination of years, months, and days to your date, you’ll be using the same DATE Excel function.

To add years, months, and days your formula should look like this:

DATE(YEAR(date) + X years, MONTH(date) + Y months, DAY(date) + Z days

To subtract them:

DATE(YEAR(date) – X years, MONTH(date) – Y months, DAY(date) – Z days

If you want to see it in the example, the following formula adds 2 years, 3 months, and 10 days:

=DATE(YEAR(A2)+2,MONTH(A2)+3,DAY(A2)+10)

=DATE(YEAR(A2)-2,MONTH(A2)-3,DAY(A2)-10)

Whether you’recalculating project deadlines, forecasting future events, or managing financial schedules, the ability to effortlessly adjust dates is a powerful asset. Keep experimenting with different functions and formulas, and refine your skills to become a true Excel maestro.

Nicolae is a Jack of all trades technology writer with a focus on hardware, programming languages, and AI image-processing software. Over the last five years, he has ghostwritten numerous tech how-to guides and books on a variety of topics ranging from Linux to C# programming and game development. Nicolae loves everything that has to do with technology and his goal is to share his knowledge and experience with others.Read Nicolae’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved