How to add or subtract days, months, and years to date in Excel?

This article provides 7 solutions to add or subtract days, weeks, months, years, or a combination of years/months/days to a date in Excel. Video Demo

  1. Add or subtract days to date with formula
  2. Add or subtract days to multiple dates without formula
  3. Add or subtract weeks to date in Excel
  4. Add or subtract months to date in Excel
  5. Add or subtract years to date in Excel
  6. Add or subtract a combination of years, month, and days to date in Excel
  7. Add or subtract days, weeks, months, or years to date with Kutools for Excel

Add or subtract days to date with formula

You may have noticed that dates are 5-digit numbers in Excel. Therefore, you can add or subtract days as easy as adding or minus the number of days in Excel.

= date + number of days

1. Select a blank cell you will place the calculating result, type the formula =A2+10, and press the Enter key.
Note: For subtracting 10 days from the date, please use this formula =A2–10.

2. If you need to apply this formula to other cells, please drag the AutoFill handle of the formula cell to these cells as you need.

Now you have added or subtracted the same number of days from these dates in bulk. See screenshot:

Easily add/subtract days, weeks, Months, or Years to date in Excel

Difficult and tedious to remember long complicated formulas in Excel? Kutools for Excel’s Formula Helper lists most common-used formulas to help you quickly calculate and solve problems in Excel, says Add years / months / weeks / days to date, Add hours / minutes / seconds to date, Sum absolute values, Find most common value, etc. Full Feature Free Trial 30-day!
ad add days weeks months years to date

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Add or subtract days to multiple dates without formula

Comparing to formulas, some Excel users prefer to add or subtract days directly without formulas. Here I will guide you to add or subtract the same number of days from multiple dates with Paste Special feature in Excel.

1. Type the number of days you will add or subtract to dates in a blank cell, says 10, and then copy it. See screenshot:

2. Select the dates you will add or subtract days, right click, and select Paste Special > Paste Special in the context menu. See screenshot:

3. In the Paste Special dialog, please check Add or Subtract options as you need, and click the OK button. See screenshot:

4. Now the number is added or subtracted to the dates, and the dates display as 5-digit numbers. Keep these 5-digit numbers selected, and click Home > Number Format box > Short Date to convert them to dates again.

Now you will see the specified number of days is added or subtracted from the specified range of dates in bulk without formula. See screenshot:

Add or subtract weeks to date in Excel

You can also apply formula to add or subtract the specified number of weeks from a date in Excel. Please do as follows:

= date + 7 * number of weeks

1. Select the blank cell you will place the calculating result, type the formula =A2+4*7, and press the Enter key.
Note: For subtracting 4 weeks from the date, please use this formula =A2-4*7.

Kutools for Excel

Stand out from the Crowd

300 Handy Tools
Solve 80% Problems in Excel
Free Trial Now

Say goodbye to terrible VBA and formulas!

2. If you need to add or subtract weeks from other dates, please drag the AutoFill handle of the formula cell as you need.

Now you have added or subtracted the same number of weeks from multiple dates in bulk. See screenshot:

Add or subtract months to date in Excel

For adding or subtracting months to dates, it’s not fit to add or subtract 30 from the dates because months contain different number of days in a year, some contain 30 days, some contain 31 days, while some contain 28 or 29 days. Therefore, we need to apply the EDATE function to deal with this situation.

= EDATE(date, number of months)

1. Select the blank cell you will place the calculating result, type the formula =EDATE(A2,3), and drag this formula cell’s AutoFill handle to apply this formula to other cells as you need.
Note: To subtracting 3 months from the date, please use this formula =EDATE(A2,-3).

2. As you see, the EDATE function will return 5-digit numbers. Please keep these 5-digit numbers selected, and click Home > Number Format box > Short Date to convert them to dates back. See screenshot:

Now you will see it has added or subtracted the same number of months to the dates in bulk. See screenshot:

Add or subtract years to date in Excel

For example, you will add 6 years to a batch of dates in Excel, you can do as follows:

= DATE(YEAR(date) + number of years, MONTH(date),DAY(date))

1. Select the blank cell you will place the calculating result, type the formula =DATE(YEAR(A2)+6,MONTH(A2),DAY(A2)), and press the Enter key.
Note: To subtract 6 years from the date, please apply this formula =DATE(YEAR(A2)-6,MONTH(A2),DAY(A2)).

2. If needed, drag the formula cell’s AutoFill handle to apply this formula to other cells as you need.

Add or subtract a combination of years, month, and days to date in Excel

In some cases, you may need to add or subtract years, months, and days at the same time, says add 3 years 5 months and 15 days. In this case, you can apply the DATE function to solve the problem.

= DATE(YEAR(date) + number of years, MONTH(date) + number of months, DAY(date) + number of days)

1. Select the blank cell you will place the calculating result, type the formula =DATE(YEAR(A2)+3,MONTH(A2)+5,DAY(A2)+15), and press the Enter key. See screenshot:
Note: To subtract the combination of years, months, and days together, please use this formula =DATE(YEAR(A2)-3,MONTH(A2)-5,DAY(A2)-15).

2. If needed, please drag this formula cell’s AutoFill handle to apply this formula to other cells.

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

If you have Kutools for Excel installed, you will find its Formula Helper lists most-commonly-used formulas to help you easily calculate in Excel, including adding days, weeks, months, or years to date. Please do as follows:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Select the cell you will put calculating result in, and click Kutools > Formula Helper > Add years to date.
Tip: Please select the suitable formula from the Formula 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 add 5 year from the date, therefore we type 5 into the Number box. If you need to subtract 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:

Kutools for Excel’s Formula Helper will save Excel users from memorizing complicate formulas and lists most common-used formulas to help you quickly calculate and solve problems in Excel, says Add years / months / weeks / days to date, Add hours / minutes / seconds to date, etc. Have a Free Trial!

Demo: Add or subtract days, weeks, months, or years to date in Excel


300 tools help you stand out from the crowd now

with Kutools for Excel - never worry about job cut

Kutools for Excel brings 300 handy tools for 1500 work scenarios, helps you work faster and better than your colleagues, and easily win trust of your boss. You will be the last one in the list of layoffs, and easily maintain a stable and better life for your family!

  • To be a master of Excel in 3 minutes, and easily win appreciation of others.
  • Improve 80% work efficiency, solve your 80% problems in Excel, not work overtime.
  • Speed up your work, save 2 hours every day to improve yourself and accompany family.
  • Say goodbye to complicated formulas and VBA code, free up your memory.
  • Reduce thousands of keyboard operations and mouse clicks, far away from mouse hand.
  • Spend $39.0, worth more than $4000.0 training of others.
  • Choice of 110,000+ highly effective people and 300+ famous companies, work stable in Excel.
  • Full feature free trial 30-day, no credit card required.
Read More ...
Free Trial Now
 
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 · 1 years 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 · 2 years 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 · 2 years 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 · 2 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.
      kelly.extendoffice@gmail.com · 2 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 · 3 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
  • To post as a guest, your comment is unpublished.
    Sangeetha · 3 years ago
    hi,

    I need help regarding dates
    I don't have kutools in my excel

    I want to add number of years with if condition, say 3 years for particular date mentioned in cell C4 having condition if the date mentioned in C4 is less than or equal to today's date/current date.

    I have tried with adding number of days
    =if(C4
  • To post as a guest, your comment is unpublished.
    Willim · 3 years ago
    Hopefully this assists with add or subtracting days, months or years. The formula in D4 is copied to all the other rows and the results are as shown. As can be seen day,months, years or combinations can be added or subtracted

    I am using Open Office but I expect it to work in Excel although perhaps not for before 1900?

    A B C D
    1 dd mm yyyy date
    2 21 4 1853 21-Apr-1853 =DATE(C2;B2;A2) "fixed date"
    3
    4 -15 -8 -63 06-Aug-1789 =DATE(C$2+C4;B$2+B4;A$2+A4) "new variable date"

    -15 0 0 06-Apr-1853 days
    0 -8 0 21-Aug-1852 months
    0 0 -63 21-Apr-1790 years

    15 8 63 05-Jan-1917

    15 0 0 06-May-1853 days
    0 8 0 21-Dec-1853 months
    0 0 63 21-Apr-1916 years
  • To post as a guest, your comment is unpublished.
    Ritika · 3 years ago
    What if we need to subtract months from year

    Say,
    If we have start date 2016
    and the resutant cell we need is Sept 2015.
  • To post as a guest, your comment is unpublished.
    Ritika · 3 years ago
    What if we need to subtract specified number of months from particular Date?
    Say, 2016 we want to fix a formula to get Sept 2015
  • To post as a guest, your comment is unpublished.
    Megha · 3 years ago
    Thank you so much for such a useful content.
    I am facing a difficulty, If I am applying formula "=MONTH(A4-A3)" with A4 as 01/10/2016 & A3 as 01/07/2016, it is returning value 4 instead of 3. Why? kindly explain & how to deal with it.
  • To post as a guest, your comment is unpublished.
    Matt · 4 years ago
    This was exactly what I was looking for - thank you so much!

    One request for help - In the US, when someone dies, the fiduciary tax return is due the following year on the last day of the month prior to the date of the person's death. So if the person dies March 15, 2015, then the tax return will be due on February 29, 2016. Is there a way to calculate that and display it in a cell?

    Thanks,

    Matt
    • To post as a guest, your comment is unpublished.
      · 2 years ago
      =EOMONTH("2015/3/15",11)
    • To post as a guest, your comment is unpublished.
      star · 3 years ago
      Formula is EOmonth(3/15/2015,11)
  • To post as a guest, your comment is unpublished.
    soso · 4 years ago
    very very interesting
  • To post as a guest, your comment is unpublished.
    Graeme · 4 years ago
    Hi Guys

    Can anyone help me continue the days of the week from a starting point that will auto calculate to other tabs in excel please?
  • To post as a guest, your comment is unpublished.
    Champika · 4 years ago
    This is great. Thank you for explaining it in an easy to understand steps.
  • To post as a guest, your comment is unpublished.
    ibasheer · 4 years ago
    Fantastic.....u made my job easy now..
    Thanks a lot
  • To post as a guest, your comment is unpublished.
    Sam · 4 years ago
    How do I write a formula from numbers of days an apartment is vacant to find out what was the original date that it was vancated?
  • To post as a guest, your comment is unpublished.
    pallavi · 4 years ago
    How to add two time periods,
    means, i have 2 years 5 months experience in past, present i have 1 year 2 months experience,
    How to add those two periods in excel.
  • To post as a guest, your comment is unpublished.
    Kim · 4 years ago
    That did not work at all
  • To post as a guest, your comment is unpublished.
    Mounika · 4 years ago
    Really Thanks a lot...Helped this Tip in awesome way.
  • To post as a guest, your comment is unpublished.
    vaishnavi · 4 years ago
    it was very helpful for me
  • To post as a guest, your comment is unpublished.
    suhier · 4 years ago
    A lot of thanks for your great effort :) but I want your kind help to explain the following ..
    What is the formula I have to use in order to automatically calculate a certain credit in a given range ( date from- to) ??
    For example I need to create a vacation system ; that gives an employee a certain credit of 28 days to be taken in 2 years ONLY, if he exceeds 2 years he will not be entitled to ask for vacation but he can use the new credit of the next 2 years & etc.. .

    Thank you for your time & consideration .
  • To post as a guest, your comment is unpublished.
    AMitesh · 4 years ago
    Hi, if i have a date in one cell (E.g 20-11-2015) and i want to add 7 days to that date as a reminder in another cell (i.e 20-11-2015+ 7 days= 27-11-2015). What is the formula i have to use to get result as 27-11-2015. Please assist.
  • To post as a guest, your comment is unpublished.
    RAJ · 4 years ago
    The formula you give works with recent dates, but will not calculate with older dates. For example, If I have a date of Jan 9, 1896 and want to subtract 60 years 9 months 30 days it does not work. It will say #VALUE!. If you change the year to 1996 it works fine. Can this be fixed?
  • To post as a guest, your comment is unpublished.
    Senthilvel · 5 years ago
    Thank u so much!! worked well.
  • To post as a guest, your comment is unpublished.
    Jaypee · 5 years ago
    Thank you so much! really nice!
  • To post as a guest, your comment is unpublished.
    CeRo · 5 years ago
    was great, very helpful to me!!!
  • To post as a guest, your comment is unpublished.
    Naseeb · 5 years ago
    excellent explanation
    But i need to know on below in case i need result date to be end of every month ..What formula to use
    Examble 1-May-2015 to be converted to 30 Apr 2016

    Also 1 Jun 2015 to be converted to 31 May 2016
    PLease help
  • To post as a guest, your comment is unpublished.
    Nilesh · 5 years ago
    Excellent and good Explanation in details..nice thank you so much
  • To post as a guest, your comment is unpublished.
    Tapas Behera · 5 years ago
    All these are good except for one - add years to date. I am not saying your solution is wrong, but there can be another solution based on the need. In case you are adding 1 year to 29th Feb 2016. Your formula will give the date 1st Mar 2017, whereas someone may look for the the end date of Feb itself. For them the below formula would work better.

    =EDATE(A2,12)
  • To post as a guest, your comment is unpublished.
    Madhu Nair · 5 years ago
    how to put date of 2015 in excel
  • To post as a guest, your comment is unpublished.
    dianne · 5 years ago
    I have dates going across a sheet; each column will eventually have a date put in it. What I want to do is have the last column-which is labeled 'Next Due Date' automatically add 90 days to the newest date entered.
    Columns are labeled 'Date Completed'
    There will be a list of names in the rows and as their Home Visits are completed and the date is entered, we want the last column to 'refresh' & tell us the next due date (90 days)
    All I can find is: example =D2+90 but then we have to change the Letter each time & we want to protect that column so nobody can mess with the formula.
  • To post as a guest, your comment is unpublished.
    AnneK · 5 years ago
    Avinash, if you know for sure that it will be the first of the month each time:
    =ROUND((L5-K5)/30,0)-1
    Otherwise, you can try this: =CONVERT(L5-K5,"day","yr")*12 but then you still need to round it accordingly. I admit that neither of the solutions is really pretty.
  • To post as a guest, your comment is unpublished.
    VENKAT · 5 years ago
    03.02.2014 how convert 03/02/2014
  • To post as a guest, your comment is unpublished.
    VENKAT · 5 years ago
    03.02.2014 how to convert 03/02/2014
  • To post as a guest, your comment is unpublished.
    Avinash Singh · 5 years ago
    Hello..

    I want to less year to year get months in excel,Exp:-01-01-2000 - 01-08-2000 = 8 months
    by which formula i do that>>>.
  • To post as a guest, your comment is unpublished.
    Sam Trieu · 5 years ago
    This is why I like Excel. Thank You for sharing. Save me a lot of time when I compute depreciation expenses for my company
  • To post as a guest, your comment is unpublished.
    Prashant Chitmulwar · 5 years ago
    Sir,
    Very informative and helping a lot.
    Thank You.
  • To post as a guest, your comment is unpublished.
    Azeem Qureshi · 5 years ago
    20/08/2014 - 10/10/2016=?

    =DATEDIF(A1,B1,"Y")&"Years,"&DATEDIF(A1,B1,"YM")&"Months"&DATEDIF(A1,B1,"MD")&"Days"
    If it not working than go to control panel then go to clock, language, Region and set your date format like 1st day, Month, Year and ok.
    It was the solution Ravi Kumar of your question.
    Reagrad
    Azeem Qureshi
    • To post as a guest, your comment is unpublished.
      vaishnavi · 4 years ago
      it was very helpful for me
  • To post as a guest, your comment is unpublished.
    Anita · 5 years ago
    thank you!!!!! you saved me!
  • To post as a guest, your comment is unpublished.
    Anil Pawaskar · 5 years ago
    Very Nice
    I am thankful
  • To post as a guest, your comment is unpublished.
    Ravi Kumar · 5 years ago
    Dear Sir/Madam,

    20/08/2014 - 10/10/2016 = days ...?

    In excel how to calculate the no of days help me in this regard.

    Regards
    Cherry,
    • To post as a guest, your comment is unpublished.
      star · 3 years ago
      In order to cal days try this formula Datedif(10/10/2016-20/8/2014,"d")+1
  • To post as a guest, your comment is unpublished.
    Lei G · 5 years ago
    THIS SIN'T WHAT I WAS LOOKING FOR, IT WASN'T USEFUL AT ALL. I WAS TRYING TO CALCULATE A TIME PERIOD . NOT SUBTRACT & ADD DATES ! THANKS BUT NO THANKS :-x
  • To post as a guest, your comment is unpublished.
    Pankaj Singh · 5 years ago
    Extremely Good..... Looking for these kind of more examples
  • To post as a guest, your comment is unpublished.
    Pankaj Singh · 5 years ago
    Really Cool yaar...... Extremely Good
  • To post as a guest, your comment is unpublished.
    kunnumpurathu · 5 years ago
    Simple but effectve explanation; thanks
  • To post as a guest, your comment is unpublished.
    Ajeet · 5 years ago
    Really so nice post... helped a lot
  • To post as a guest, your comment is unpublished.
    Rama · 5 years ago
    Thank you so much for your explaining with screen shots.
    Really so much helped this tip to me..
  • To post as a guest, your comment is unpublished.
    NIK · 6 years ago
    thanks EDATE function solved my problem

    thanks a lot.
  • To post as a guest, your comment is unpublished.
    Sneet · 6 years ago
    This is what I was looking for. Thanks, it is really useful. :-)
  • To post as a guest, your comment is unpublished.
    Lizzy · 6 years ago
    Thank you, thank you, thank you. This is exactly what I needed and explained in such a clear manner with explanations and examples. Has made life so much easier without my brain imploding :lol: