Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to add / subtract days / months / years to date in Excel?

Supposing you have a list of date in your worksheet, and now you want to add or subtract some days or months for adjusting the date to your need. You can add or subtract days, months or years by using following simple formulas in Excel.

Add or subtract days/months/years to date in Excel

Add / subtract a combination of days, months, and years to date in Excel

Add/subtract days/weeks/months/years to date with Kutools for Excel

Easily add/subtract hours, minutes, seconds to time in Excel

Provided by Kutools for Excel. Full Features 60-day Free Trial!
ad add subtract hours minutes seconds 1


arrow blue right bubble Add or subtract days/months/years to date in Excel

Normally, it is easy to add or subtract certain days / months / years to the date in Excel, you just need to add or subtract the number of days to the date directly.

Here I take the date of 2012/10/23 in Cell A2 for example, and list corresponding formulas to add or subtract certain days / months / years to this date in following table:

  A B C D
1 Date    
2 2012/10/23      
3 No. Add/Subtract Formula Formula returns
4 1 Add 100 days =A2+100 2013/1/31
5 2 Subtract 100 days =A2-100 2012/7/15
6 3 Add 8 months =EDATE(A2,8) 2013/6/23
7 4 Subtract 8 month =EDATE(A2,-8) 2012/2/23
8 5 Add 15 years =DATE(YEAR(A2)+15,MONTH(A2),DAY(A2)) 2027/10/23
9 6 Subtract 15 years =DATE(YEAR(A2)-15,MONTH(A2),DAY(A2)) 1997/10/23
note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial
See following screenshots:

Note: The formulas of =EDATE(A1,8) or =EDATE(A1,-8) will return 5-digits number such as 40962. You can easily change the formula results to date format with Home > the Number Format box > Short Date.


arrow blue right bubble Add / subtract a combination of days, months, and years to date in Excel

To add or subtract a combination of days, months and years to the date, you can use the following formula:

=DATE(YEAR(start_date) + add_year, MONTH(start_date) + add_month, DAY(start_ date)+ add_day)
(1) start_date: A date or cell reference that contains a date.
(2) add_year: The number of years to be added.
(3) add_month: The number of months to be added.
(4) add_day: The number of days to be added.

In this example, I will add 3 years 5 months and 15 days for the date. Now I enter the formula =DATE(YEAR(A2)+3,MONTH(A2)+5,DAY(A2)+15) into Cell B2, and then drag the Fill Handle to the range needed this formula.See screenshot:

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

Note: To subtract a combination of days, months, and years, enter a negative number, for example =DATE(YEAR(A2)-3,MONTH(A2)-5,DAY(A2)-15).


arrow blue right bubble Add/subtract days/weeks/months/years to date with Kutools for Excel

Kutools for Excel's Commonly-used Formulas collects four formulas for easily adding or subtracting days/weeks/months/years to date easily without remembering complicated formulas in Excel.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the cell you will put calculated result in, and click Kutools > Formulas > Add years to date.
Tip: Please select the suitable formula from the Formulas Helper drop down list based on your needs. For example, select Add weeks to date for adding/subtracting specified weeks to a date.

2. In the opening Formula Helper dialog box, specify the date cell address in the Date Time box, and type the number of years you will add in the Number box.
Tip: In our case, we will subtract 5 year from the date, therefore we type -5 into the Number box. If you need to add some years, for example 3 years, please type 3 into the Number box.

3. Click the Ok button to apply the formula of Add years to date. Please drag the Fill Handle to apply this formula to other range if necessary.

Add/subtract days to date:

Add/subtract weeks to date:

Add/subtract months to date:


arrow blue right bubbleDemo: Add/subtract days/weeks/months/years to date with Kutools for Excel

Tip: In this Video, Kutools tab and Enterprise tab are added by Kutools for Excel. If you need it, please click here to have a 60-day free trial without limitation!



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    shalu · 4 months ago
    i need your help, i want to solve one question please tell me
    one boy is going on leave from 26-06-2018 to 22-07-2018 for 27 days and his total monthly salary is 23968

    if the month has 30 days then his salary will be per day 798.93 & if month has 31 days then 773.16

    he was on leave in july 05 days(30 days in the month) and rest 22 dyas in july(31 days in the month)
    23968/30*5+23968/31*44
    i want then formula for this calculation in one cell please tell me as soon as possible because its urgent for me mail me on my personal mail
  • To post as a guest, your comment is unpublished.
    Jeff · 8 months ago
    I used the DateDif to get the number of years, months, and days. Now I'm trying to total all of those years months and days. How to I do the total of all the columns for years months and days
  • To post as a guest, your comment is unpublished.
    Brad J · 11 months ago
    I am trying to use the add month function, but I want the date to be based on the previous tab, so when I copy a new tab, it automatically updates the date based on adding a month to the previous tab's date. Any suggestions? Thank you - Brad
  • To post as a guest, your comment is unpublished.
    shashikant · 1 years ago
    i have a date but i want to be after 120 days, date ..what i mean to say for example ...date 7.11.2017 + days 120 = ?(here which date) ..which formula will i use ?
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi,
      If the date is in a certain cell, say A1, you can use the formula =A1+120 directly;
      If the date is not in a cell, you can use this formula =DATE(2017,11,7)+120
  • To post as a guest, your comment is unpublished.
    WILLIM · 1 years ago
    For simple calculations
    1 year = 365.25 days
    1 month = 365.25/12
    ...
    cell C1 =now()
    Cell C4 = date in question
    =IF(C4