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 Excel can sometimes feel like navigating a labyrinth, especially when you need to add or subtract 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.
Adding Days to a Date in a Cell
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 Days from a Date in a Cell
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 like using 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:
- In a blank cell, in column B, type the number of days you want to add or subtract from the dates, Then copy it. You can right-click it and select copy, or just press Ctrl + C on your keyboard.
- Select the range of cells containing dates to which you want to add or from which you’ll subtract days.
- Right-click your mouse on the selected cells, choose Paste Special, and then again Paste Special at the bottom of the context menu.
- When a Paste Special dialog box opens, check Add or Subtract option (depending on what you’re trying to achieve), and click the OK button in the bottom left corner to close the window.
- You’ll notice the dates are now displayed as 5-digit numbers, but the number of days is added to them.
- Select these 5-digit numbers, select Home, then Number Format box, and select Short Date format from the drop-down menu. This will convert the numbers into dates again.
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.
Add or Subtract Months with the Excel DATE 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))
Add or Subtract Months with the Excel EDATE Function
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’re calculating 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.