Comments on: How to add and subtract dates, days, weeks, months and years in Excel

In this tutorial, you will find a variety of useful formulas to add and subtract dates in Excel, such as subtracting two dates, adding days, months and years to a date, and more. Continue reading →

Comments page 2. Total comments: 142

  1. Sir,
    Is there any possibility for subtracting /adding academic years..
    For Example.
    In A1 value is given as 2010-2011
    If there any formula to get 2011-2012 or 2009-2010.

    Thanks in Advance

  2. How to calculate 3 years back date next month 1st date.

    For example if date is 25/12/2021, 02/12/2021 if any date in month of Dec 2021 then date is 01/01/2019

    And any dates in month Jan 2022 then it's 02/01/2019

    Is there an easy way to calculate the 3 years back next month 1st date.

    Please help me.

    1. Hi!
      You may read how to subtract 3 years of the date in this article above.
      To determine the last day of the month, use the EOMONTH function.
      Then add 1 day.

      =EOMONTH(DATE(YEAR(A1)-3,MONTH(A1),DAY(A1)),0)+1

  3. I have one task need to complete it within 5 days.

    If task create date is 13/12/2021 (Monday)then we will complete it 17/12/2021 (Friday) or before.

    But problem is if task create date is 14/12/2021(Tuesday) & 15/12/2011(Wednesday) then deadline is 18th and 19th Dec Weekend.

    If deadline is on weekends then we will complete this task on Friday.

    How can I add excel formula on this?
    Could you help me on this

  4. how to deduct 27/04/2020 14.00 hrs ( dd.mm.yy and 24 hours format) from 01/05/2020 10.00 hrs

  5. I'm working in Google Sheets, so this may not have the capacity to do this. Let's say I have a date in cell A1 as 1/11/2021, I need Cell B2 to show a date that is 6 days from the date in cell A1. I know how to enter the formula, that's easy as =A1+6 and returns a date of 7/11/2021. If the cell in A1 is left blank, the formula in cell B2 shows up as 05/01/1900. Is there a way to have the formula in cell B2 remain blank until there is something entered in Cell A1? I can do a work around and make the text white in cell B2 so it doesn't show and then make it black to reveal the dates when I have entered what needs to be entered into Cell A1. That's probably a very clumsy way of doing it though.

    So my question put simply is how do you enter a date formula into the cell and have nothing showing until has data to return as result from in the cell before it?

    Thank you for considering my question.

      1. Conditions my last date like 8/12/2021 after 12 days date than 11 days date than 8 days date but without friday. Whice formula use for this. Pls replay.

  6. Hi, i need to sum days to a date but just in workdays.

  7. Hi
    Both of below are not working, please help
    =DATEDIF(B2, TODAY(), "d")
    =TODAY()-B2

  8. Hi. I'm getting a #value! error message when I input this formula =DATE(YEAR(L25) - 1, MONTH(L25), DAY(L25)).

    L25=Aug 19 2022

    I'm simply trying to reduce the new date by a year. Why am I getting the error message?

    Thanks

  9. I have specific date in one cell, from there i need to go 4 weeks forward. Use =Date function i have just calculated 4 weeks. but my question is, if those four weeks falls in december month in between , i want to add 7 (1week)more days extra due to shutdown holidays... how to enter this scenario in exisiting date range formula?

    existing formula - =DATE(YEAR(D39),MONTH(D39),DAY(D39)+28)
    my commit date D39 - 30/11/2021
    I got 28th dec as output... but
    my expected date - 4th jan 2022

  10. Hi,
    is there a formula where by I subtract dates from a set date (meeting date) and can establish which date I need to request reports showing only days that are monday-friday?

    I.e.meeting date is 10/04/2020 and i need to request the reports 3 weeks before that date I would normally use 10/4/21 cell reference - 21 but this often gives me weekends or holidays

    please help

    1. Hello!
      If you have a five-day work week, then you can use the formula

      =WORKDAY.INTL(A1,-15,1)

      You can learn more about calculating weekdays in Excel in this article on our blog.

  11. I have a start date (which passed years ago) and a end date but need to work out how many months are left from today ?

    1. Hello Lynne!
      Use the DATEDIF function to calculate the difference between dates in months. For detailed instructions, see here.

  12. Why are there so many errors on this page?
    > date) + N years, MONTH(date), DAY(date))

    Where's the beginning of that line????

  13. HI,
    I would like to get the difference in the dates "4/13/2019" and "2/25/2019" so used function "=DAYS("4/13/2019","2/25/2019")", but getting the result like this .."2/16/1900", i was expecting 47, but no matter which function(Tried with today(), simply subtraction) i am using, i am seeing the same result.. can some explain me why so?

    1. Always make sure to check the formatting of the cells. This can be fixed by changing the date format to a number.

  14. I need to calculate the total deal cost for deals with a start date and end date and an annual recurring rate. Leap years need to be taken into account. Deals are usually between 12 months and 48 months. I would like to see a result like 3.26 year where all years are calculated as if they are being 365 days. Hope you can help.

    1. DATE(YEAR([@[Contract Start]]),MONTH([@[Contract Start]])+12,DAY([@[Contract Start]])-1)

      1. Wim, I was solving for one year, less one day. This is the formula I was able to get to factor in leap year. I'm not able to delete my reply, so I'm not sure it applies to you.

  15. I have the following in cells A1 (start date and time) and B1 (finish date and time)
    A1: 29/12/2017 11:44
    B1: 02/01/2018 08:00

    What formula can I use to get the total time from A1 to B1 in HH:mm?

    1. I found this to work though giving decimal values
      =DAYS(B1,A1)*24+HOUR(B1-A1)+MINUTE(B1-A1)/60

  16. Hi, I'm looking for a formula to calculate when a payment is due when it's 30 days after month end.
    Eg.. Invoice date 15th May,the due date would be 30 days after month end..
    So I trust the actual date would be 30th June.. Is there a formula for this.?

    1. Karen:
      The formula you're looking for is:
      =EOMONTH(A22,1)
      Where 5/15/18 is in A22 and is formatted as Date as is the cell containing the formula.

  17. Kindly help me in sorting out this issue I want to calculate number of days between eg.29/11/2017 to 09/11/2017

    1. Just do latest date - earliest date
      e.g. Cell A1 contains 29/11/2017, Cell B2 contains 09/11/2017
      Do =A1-B2 the result will be the number of days (ensure the result is in 'General' format)

  18. Do you have any formula to increment year of date by some no. like EDATE for month ?

  19. I have an Excel Spreadsheet that I am using to track success completing multiple tasks by companies. I have built a spreadsheet with a "Date Due" and a "Date Completed" column for each task. I already know the due dates and have populated the same. The dates items are completed are entered when done. I then calculate the difference between the Date Due and the Date Completed. I have built the simple formula of =Sum(D4-E4). I built the spreadsheet and copied the formula to all columns/cells that will eventually be populated. Here is my question, how do I get the cells that include the formula to return a 0 or nil value if the E4 value in the formula above is not populated? This means my work is still in progress and I don't have a "Date Completed" value. I am assuming an IF statement will do it, but I would like some guidance/recommendation . . .

    Thanks,

    1. Hello, Joanne,

      Please try the following formula:

      =IF(ISBLANK(E4),"0",E4)

      Hope it will help you.

  20. I have calculated difference between two dates using datedif formula
    but Iam having problem calculating difference from the result that i got from above formula like eg. (dd/mm/yyyy)

    Start date = 20/04/1983 End date = 31/03/2017
    the result i got is 33year, 11months, 11days. Now I want to substract 49 days from the result which Iam not able to do it. Please help me solve the calculation.

    1. Hello,

      Please try the following formula:

      =DATEDIF("20/04/1983",("31/03/2017"-49),"Y") & "years, " & DATEDIF("20/04/1983",("31/03/2017"-49),"YM") & "months, " & DATEDIF("20/04/1983",("31/03/2017"-49),"MD") & "days"

      Hope it will help you.

  21. I have a column formatted as date YYYY-MM-DD, and I am using your formula to add 50 to the year.
    I used
    =DATE(YEAR(E2)+50,MONTH(E2),DAY(E2))
    I thought this would be simple, but I am getting a Value error. I am obviously missing something simple, but I am having one of those days

    Any help would be appreciated. Thanks

    1. I discovered the problem, Excel thinks the world started after 1900. My dates were 1800s and Excel does not recognize anything before 1900 as a date.

  22. Thank you for a very interesting article and website.

    If I put 3/31/2017 in A1, the formula =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))in B1, and the formula =EDATE(A1,1)in C1, I get 5/1/2017 for B1 and 4/30/2017 for C1.

    I was expecting the same result for each formula.

    Why is there a difference when both formulas are supposed to yield the same, namely one month from 3/31/2017?

    What am I missing?

    1. Hi, James,
      it is a very interesting notice! DATE and EDATE functions treat the months in slightly different ways. EDATE adjusts the results by adding a whole month (but since April consists of 30 days, its last day is shown). DATE, in its turn, adds the same number of days, but the 31st day is treated by the function as the 1st day of the next month. Please, look at the example below to get a clear understanding.

      DATE and EDATE functions results

  23. Hi I need a formula to know the number of days a project is delayed
    Ex.
    No. of day required days = 7 days
    Start date = March 20, 2017
    End date = March 24, 2017

    What formula can I use to reflect no. of days a project is advanced or delayed. Excluding Sat Sun and holidays

    1. Hi Ron,

      Let's say that start day is in A1, end date is in B1.
      First of all, you need to count the number of working days between your dates in C1:
      =NETWORKDAYS(A1,B1)
      You can find out how to exclude weekends and holidays here.

      Then, depending on how you want the result to return:
      a) if as a text in D1, enter the next formula there:
      =IF(C1<=7,"Advanced","Delayed")

      b) if you want to fill C with a colour, create formatting rules using formulas:
      =AND($C1<=7,$C1<>"") (green colour for advanced)
      and
      =AND($C1>7,$C1<>"") (red, meaning delayed)
      Apply the formatting for =$C:$C column.

      Hope it helps!

  24. Well after googling several add month solutions it appears that the 2 solutions actually do not work as I would expect them.

    How do you actually get the last day of the month based on the last day of the previous month ?

    If I add 1 month for example to the last day of September 2015 i.e:
    A1=date(2015,09,30) := 2015-09-30
    A2=date(year(A1),month(A1)+1,day(A1)) := 2015-10-30
    This is 1 day before the last day of October 2015.
    So it isn't adding 1 month but 1 month minus 1 day.

    I was expecting to get as a result: 2015-10-31

    Same result using EDATE
    A1=date(2015,09,30) := 2015-09-30
    A2=EDATE(A1,1) := 2015-10-30

    The only way I found to get this to work i.e: get the date of the last day of the month based on previous last day of the month is:

    A1=date(2015,09,30) := 2015-09-30
    A2=DATE(YEAR(A1+1),MONTH(A1+1)+1,DAY(A1+1))-1 := 2015-10-31

    1. The EOMONTH formula will return the last day of the month, and you can specify how many months before or after your original date. e.g. if your original date is in cell A1, =EOMONTH(A1,3) will return the last day of the month three months later. I hope that helps.

  25. Hi,
    How do I get the below in excel,
    suppose I have a date in A1 as 01/25/2017 13:00 and in B1 I have time to be added as 56:15 (56 hours and 15 mins), i have to add these hours and mins to A1 and get the result as 01/27/2016 21:15

    1. I want to add hours to date & time

  26. Hi all,

    Following this thread and trying to create a list in Column A to reflect a weekly schedule with the value in A3 being the baseline and incrementing A4, A5, etc... by 7 days, simple stuff based on this site... so I thought... ;)

    Desired results
    Cell A3 - 1/10/2017
    Cell A4 - 1/17/2017
    Cell A5 - 1/24/2017
    etc...

    Current configuration
    Cell A3
    - Format: Date (3-14-2012 from Date format Type list)
    - Current value: 1/10/2017

    Cell K1
    - Format: Number
    - Current value: 7

    Cell A4
    - Format: Date (3-14-2012 from Date format Type list)
    - Formula: =DATE(YEAR(A3),MONTH(A3),DAY(A3)+$K$1)
    - Resultant value: #VALUE!

    Investigation
    I'm on Office2016 64-bit on a WINDOWS7 64-bit, i7 CPU/16GB RAM

    If I go to Cell A3 and "drag down" to A4 the date increments to 1/10/2018 so it seems as though it is recognized as a date...

    Looking at the Function bar dialog box when you click on "fx" it shows:

    /snip

    DATE
    Year: YEAR(A3) = #VALUE!
    Month: MONTH(A3) = #VALUE!
    Day: DAY(A3)+$K$1 = #VALUE!

    Formula result =

    /snip

    I then look at "Evaluate formula" and get (CY2017 is the tab name):

    /snip

    Reference Evaluation
    'CY2017'!$A$4 = DATE(YEAR("1-10-2017"),MONTH(A3),DAY(A3)+$K$1)

    The next evaluation will result in an error.

    /snip

    No change if I modify Date "type" format or change "/" to "-"...

    Questions
    Why would the date not format to 1-10-2016 per the selected Date format?
    - I went to a blank cell and modified the cell format to the same as above, when I then enter 1/10/17 it changes to 2001-10-17... huh??

    Appreciate any insight, thanks.

    --
    Dave

    1. OK, all I can say is WOW... :) I got this to work but it seems to me to have been MUCH more complicated than need be...

      Additional/changed config (from previous post)
      Cell J1
      - Format: Date (*2012-03-14)
      - Current value: 2017-01-10

      Cell A3
      - Format: Date (3-14-2012)
      - Formula: =DATE(YEAR(J1),MONTH(J1),DAY(J1)+0)
      - Resultant value: 1-10-2017

      Cell A4
      - Format: Date (3-14-2012)
      - Formula: =DATE(YEAR(A3),MONTH(A3,DAY(A3)+$K$1)
      - Resultant value: 1-17-2017

      I then drag A4 down to fill out the rest of the year and it works.

      Really feels like this was way more complicated than need be, thoughts?

      --
      Dave

  27. HI,,,
    Hope you doing well in the best of health. I really like the page, which is much helpfull. I need help please guide me.
    I am doing job at college and we deal majority student matters.
    if a student deposit his/her fee after due date, and due date is e.g 25-09-2016 and deposit date is 27-10-2016. each day after due date will b charge as fine Rs. 100. how i will do it with formula........please help

    1. Hi Naveed,

      Assuming the due date is in A2, and the actual payment date in B2, and the fine is 100 per day, you can calculate the fine amount with this formula:

      =IF(B2>A2, (B2-A2)*100, 0)

      If necessary, you can add the fine to the original amount, which is, say, in cell C2:

      =IF(B2>A2, (B2-A2)*100+C2, C2)

  28. Hi there...Hope you can help me with the following.

    I want to add a date to a cell. Then in a different cell i want it to work out 6 months from the date in the first cell. IF the date then goes pass our current date, i want it to show red or say expired.

    1. Hi Lorien,

      Supposing you add the first date to A1. Then you can enter the following formula, say in B1, to add 6 months to the first date:
      =EOMONTH(A1, 6)

      And then, you can create a conditional formatting rule with the below formula to highlight the date in B1 with the red color when it goes past the current date:
      =$B1<TODAY()

      For the detailed instructions, please see How to conditionally format dates and time in Excel.

  29. I am trying to subtract two dates to get a calculated time between the dates in MM:dd:hh:mm format to see how long it takes to resolve an ticket. Example:

    Start Date: 05/01/2016 13:24
    End Date: 05/17/2016 18:04

    Should end up with 00:16:04:40, but it comes out as 01:16:04:40, and I can't for the life of me figure out how to make that month be 00 and be more accurate.

    I have found several articles that use the ability to manipulate the month itself, but doesn't describe how to subtract two dates AND round down on the month to show the accurate time between the two dates. Any help here would save what hair I have left. Thanks.

    1. Hi Sandy,

      You can use DATEDIF to get the number of complete months between 2 dates, and then concatenate the results of several functions in 1 formula:

      =DATEDIF(A1,A2,"m") & ":" & DATEDIF(A1,A2,"d") & ":" & TEXT(TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) - TIME(HOUR(A1), MINUTE(A1), SECOND(A1)), "hh:mm")

      Where A1 is the start date, and A2 is the end date.

      The drawback of this approach is that the result is a text string and you won't be able to use it in other calculations.

      1. Yes that's the problem. I do need to be able to add the results of this together to get a total time spent on a ticket. Any other ideas?

        1. Sorry, I have no other idea. To my best knowledge, none of the Excel functions or formats can display a zero month or day.

          As a workaround, you can use 1 column to present the time difference in an easy-to-understand way (DATEDIF formula). And in another column, calculate the time difference with a simple formula like =A2-A1, and display it in hours and minutes [hh]:mm. And then, use that second column to get the total time.

  30. Hi! Is it possible to do math with dates which are formatted 3.4.2016? So separated with dots in other words.

  31. hi,

    Wednesday, June 01, 2016
    i want to split day and date so how to possible ?

    1. Hello Hiren,

      If your original values are dates, you can use the following formulas:

      To extract the day of the week: =TEXT(A2, "dddd")
      To extract the date: =TEXT(A2, "mmmm dd, yyyy")

      If your original values are text strings, then use these ones:

      To extract the day of the week: =LEFT(A2, FIND(",", A2)-1)
      To extract the date: =RIGHT(A2, LEN(A2)-FIND(",", A2)-1)

  32. =IFERROR(MONTH(BA5),"") IS RETURNING 1

    1. Hi Dan,

      Just use IF instead of IFERROR:

      =IF(BA5="","", MONTH(BA5))

  33. How do i get a a cell to show a specific day before a specified date.

    I need a cell to show the Thursday before any specified date.

    IE cell C2 needs to show the date for the Thursday before the date in cell D2

    Thanks
    Ash

    1. Thanks Samantha, that's not quite what I mean though.

      Say I have today's date (24/04/2016) in cell D2, I need cell C2 to tell me the date for Thursday last week (21/04/2016) but also if I have say tomorrow or Wednesdays date in D2 I still need it to return the previous Thursday (21/04/2016)

      Hope that makes sense

    2. suppose you have date in D2 then you can put the formula in C2
      =text(D2,"dddd")

      help-
      "d" - only date will come
      "dd"- only date will come
      "ddd"- Mon, Sun. wed
      "dddd" - Monday, Tuesday, saturday

  34. Joining Date last date Previous Experience Total Year Experience Grand Total Experience
    25-May-14 1-Jan-16 5 years 3 months 1 years, 7 months, 7 days
    7-Oct-14 31-Mar-16 4 years 2 months 1 years, 5 months, 24 days
    21-Jan-15 24-Mar-16 3 years 9 months 1 years, 2 months, 3 days

    please help how to add Previous Experience to Total year experience....

    1. Sorry, it's hard to think about ways of getting data without seeing your table. If the "Previous Experience" column has a string value, you can try the following:

      To get the number of years and months from Previous Experience you can use formulas like these:
      =LEFT(D3, SEARCH(" ", D3, 1)) - for years

      =MID(D3, SEARCH("years", D3, 1)+5, SEARCH("months", D3, 1) - (SEARCH("years", D3, 1)+ 5)) - for months

      If the "Total Year Experience" column is countable, add the results to the formula for this column.

  35. I want to add X number of days to the date is cell A2, showing the resulting date in cell B2. However, if I use =A2+1 (for example) to add one day to the date in A2, but no date has yet been entered in A2, then the display in B2 is "VALUE". Is there a way to not have that display, leaving B2 blank until a date is entered in A2, whereupon B2 would display the date according to the formula?

  36. I need a formula to identify any help desk ticket created between today at 12:01am and a week ago at 12:01am.

    So, in essence, I need to create a formula that says this:
    (If Ticket_Opened_date is between (today at 12:01am - 7 days) and (today at 12:00am), "New", "Old").

    The trouble is the part about it being at 12:01am and making sure the formula is dynamic so I don't have to recreate it each week. Note: Please trust me when I say I can't do it via the standard date filter options (so don't even bother suggesting that).

    Can someone help?

    1. Hello, Ruthie.

      I think that the following formula should work for your task:

      =IF(AND(D17>(NOW() - HOUR(NOW())/24 - MINUTE(NOW())/24/60 + 12/24 -7),D17>(NOW() - HOUR(NOW())/24 - MINUTE(NOW())/24/60 + 12/24 -7)), "New", "Old")

  37. Just like the comment 3 above i need to calculate the differences between dates to get a format like 2yrs, 3months, 5days, 7hrs and 25min

    for example difference between 20/10/2014 9:34 and 13/12/2016 17:20.to get
    2years, 1month, 23days, 16hrs, 14min.

    1. Hello, Bola,

      Please try this formula:
      =IF(DATEDIF(A2,B2,"y")=0,"",DATEDIF(A2,B2,"y")&" years ")&IF(DATEDIF(A2,B2,"ym")=0,"",DATEDIF(A2,B2,"ym")&" months ")&IF(DATEDIF(A2,B2,"md")=0,"",DATEDIF(A2,B2,"md")&" days") &" " &TEXT(ABS(A2-B2), "h") & " hrs" &" " &TEXT(ABS(A2-B2), "mm:ss") & " min "

  38. Is there a way to calculate the number of years between four dates? I want to calculate years of employee service where there was a lapse in employment:
    Start date 8/22/2005, End date 8/13/2008 = x years PLUS Start date 7/1/2009, End date 1/19/2010 = TOTAL YEARS. I tried adding two dated ifs together but it didn't work EG: =DATEDIF(A1,A2,"Y")+(DATEDIF(B1,B2,"Y"). (Answer s/b 3 years) Thx!

    1. Hello, Barbara,

      DATEDIF(A1,A2,"Y") returns the number of complete years. In your case 2+0 is 2. To get the correct result you need to sum 2 intervals and count the number of complete years using DateDIf:
      =DATEDIF(0,(A2-A1+B2-B1),"Y")

  39. Hi,

    I have some issue to while calculating "FROM" "TO" example :
    FROM 02/01/2016 11:14:13 AM
    TO 05/01/2016 11:14:13 PM
    How can I separate in above date
    - How many Days
    - How many Hrs.
    - How many minutes

    Thanks

    1. You can follow below steps-
      1. Formate cells- Select date formate as (MM/DD/YY TIME)(03/14/01 1:30PM) change for both cells.
      2. Type below formula as it is-
      =INT(B3-A3)&"Days,"& HOUR(B3-A3) &"hours,"&MINUTE(B3-A3)&"Minutes and"&SECOND(B3-A3)& D3"Second"

      3.If your date is 1st Feb to 1st May then result as below-
      From To Result
      From To Result
      2/1/16 11:14 AM 5/1/16 11:14 PM 90Days,12hours,0Minutes and0Second

      4.And If your date is 2nd Jan to 5th Jan then result as below-
      From To Result
      1/2/16 11:14 AM 1/5/16 11:14 PM 3Days,12hours,0Minutes and0Second

      Regards
      Mangal

  40. Hello,
    how do I determine the difference between 2 dates, and also show a "0" if the start date is greater than the end date

    1. Hi Mechele,

      You can use a simple IF formula, for example:

      =IF(A1>B1, 0, B1-A1)

      Where A1 is the start date and B1 is the end date.

      In a similar manner, you can use the DATEDIF function to calculate the difference between two dates in days, weeks, months or years:

      =IF(A1>B1, 0, DATEDIF())

  41. HELLO, I try count between two date (10-12-1990 & 5-1-2016) by use datedif in many computer the result some computer true (25 year 0 month 30 day) and another computer the result false (25 year 0 month 143 day) why the day (143) I cant find the place changed properties in excel to get to same first result.

    .....thank for answer me.

    1. Hello, Salah,

      The problem may be caused by the difference in Date formats. Please send us your formula to check.

  42. Hi

    12/21/2015 8/15/1975

    40-Yrs And 4-Months

    =DATEDIF(E11,D11,"y")&"-Yrs"& " And " & DATEDIF(E11,D11,"ym")&"-Months"

    May be helpful :
    Interval-Meaning
    m Months
    d Days
    y Years
    ym Months Excluding Years
    yd Days Excluding Years
    md Days Excluding Years And Months

    1. Why doesn't it works with negative yrs/mos/days?

    2. It doesn't work with negative yrs/mos/days

    3. Wow.... Wonderful. Thank u

    4. Wow, thank you for sharing this. :)

  43. How to subtract two dates like

    12/21/2015 - 8/15/1975

    How to get the answer to be 40 Yrs and 4 Months

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)