How to Add or Subtract Dates in Excel?

There are many instances in which you may require to add or subtract dates in Excel datasheets. You may need to:

• calculate the deadline of any task
• set a target for a goal in the future at a particular date
• predict future sales and data, the usages are endless.

Manually finding the difference between times or dates will not be feasible if the dataset you’re working with is large.

But to your rescue, Excel has features or functionality to add or subtract, days, months, years to or from a date, easily and effectively. Follow this step-by-step tutorial to learn – how to add or subtract dates in Excel, at a snap of your fingers.

Note: The methods below work on all Date Formats in Excel.

Also read: How to change Date Format in Excel

Steps to add or subtract dates in Excel

Suppose, you need to make an Excel sheet to keep track of your F.D. (Fixed Deposit) in your Bank Account. Suppose you need to keep track of the books in and out of your library.You will need to add or subtract days, months, years to or from a Date.

1. Add or Subtract Days from a Date

• To add or subtract days from a date – Enter the dates in a column (cell)
• Next, input the days in number in another column which you want to add or subtract to/from the date
• Input days with a negative sign (-) in which you want to subtract the dates [in our example the 2nd row with -20 days]

• Now, in a new cell column (here, using Expiry Date column) display the result.
• Apply the addition of column 1 (dates) and column 2 (days). In our case, it’s A3 + C3, and copy down as needed for other cells, to apply the same formula.

Add or Subtract Days to/from a Date: Date + N, N is the number of days to be added.

2. Add or Subtract Months from a Date with the EDATE function

• In order to add or subtract months to/from a date we need to use ‘EDATE‘ function
• In the output column cell (here, Expiry Date), apply EDATE function from the insert function option

The EDATE function takes two arguments (start_date, months) where the start date is the base date for calculation (here, Issue date) and the number of months that you want to add or subtract.

Enter a negative (-) value in the number of months to subtract (as shown in the 2nd data row in the below example).

3. Add or Subtract Years from a date

• Add or subtract years to or from a date in Excel, this is a bit not as easy as the above two methods, and you need to pay close attention when substituting values.
• To perform this operation we use the DATE function, which takes three arguments:

=DATE(year, month, day)

We will apply some modifications to this in-built function of Excel, where:

• year will get replaced with YEAR(date) + N years to be added.
• month will get replaced with MONTH(date) function.
• day will get replaced with DAY(date) function.

So, the final function will be:

=DATE(YEAR(date) + N years, MONTH(date), DAY(date))

As we are only concerned with the addition of years, we do not need to change months or days.

Again, to subtract years you need to enter a negative (-) value of years (N).

4. Add or Subtract day, month, years combination to/from a date

There are situations where you need to add/subtract days, months, years to/from a date in Excel, and this can be done using the formula:

=DATE(YEAR(date) + N1 years, MONTH(date) + N2 months, DAY(date) + N3 days)

N1, N2, N3 are the years, months, days to be added or subtracted.

If you have understood well, the above method to add years to date then this one would be a piece of cake for you.

Use negative values of days, months, years to subtract from a date.

Conclusion

That’s It for this tutorial! Hope you have understood well all the methods to add days, months, years to a date in Excel.