Comments on: Excel DATEDIF to calculate date difference in days, weeks, months or years

In this tutorial, you will find a simple explanation of the Excel DATEDIF function and a few formula examples that demonstrate how to compare dates and calculate the difference in days, weeks, months or years. Continue reading →

Comments page 2. Total comments: 156

  1. I have a list of dates for car payments occurring bi-weekly. My goal is to count any month that contains an instance of 3 dates. I have no idea how to make a formula for this. I've been Googling this for an hour and can't find an answer.

    Example:
    2018-09-13
    2018-09-27
    2018-10-11
    2018-10-25
    2018-11-08
    2018-11-22
    2018-12-06
    2018-12-20
    2019-01-03
    2019-01-17
    2019-01-31
    2019-02-14

    There are three car payments in January. I need to create a countifs for recurring months exceeding two date entries.

    This is literally just one column with dates in it under a heading called "Car Payments." In a separate area, I need to use a function to show which months appear more than twice.

    Does anyone know how to do this?

    1. Rini:
      Where the dates are in the range O11:O22 use this formula to count the number of times January 2019 appears in the list.
      =COUNTIFS($O$11:$O$22,">=1/1/2019",$O$11:$O$22,"<=1/31/2019")
      You can enter, "Jan 2019 Car Payments" in an empty cell to the left or right of the cell that holds this formula.
      Then you'll see the number of time Jan 2019 appears in that range. By copying this formula and pasting it into another cell and changing the dates you can do the same thing for all the dates for which you're interested in getting a count.
      Note I've got the range in an absolute reference by using the dollar signs. If you want your range to extend beyond the 11 cells in that range change the $O$22 to a different address.
      Be mindful of how you copy and paste this formula because there should not be a break after the COUNTIFS function.

  2. How do I calculate the number of days between two dates in two columns?

    1. Helen:
      Where the oldest date is in A1 and the newest date is in B1, put this in C1:
      =DATEDIF(A1,B1,"D")

  3. I need to find the difference between two dates in weeks and days. Can you help me?

    1. Fawn:
      Where start date is in A1 and end date is in B1 enter this in C1 for days
      =DATEDIF(A1,B1,"D")
      enter this in D1 for whole weeks
      =ROUNDDOWN(DATEDIF(A1,B1,"D")/7,0)

  4. Hi,
    Can someone help me to that I want to find the days in between my start date & today date.
    I tried

    =DATEIF(today(), “3/6/2018” , “d”)
    But the result is #NAME?
    Thanks!

    1. Hi Mavis,

      Please first note that you have a typo in the function name. Besides, if your start date is prior the today's date, you need to change the position of the formula arguments. Thus, the correct formula should look like this:

      =DATEDIF("3/6/2018",TODAY(),"d")

      Hope this will work for you.

  5. Here's my version:

    =
    IF(DATEDIF(A2,B2,"y")>1,DATEDIF(A2,B2,"y")&" years, ",
    IF(DATEDIF(A2,B2,"y")=1,DATEDIF(A2,B2,"y")&" year, ",
    IF(DATEDIF(A2,B2,"y")1,DATEDIF(A2,B2,"ym")&" months, ",
    IF(DATEDIF(A2,B2,"ym")=1,DATEDIF(A2,B2,"ym")&" month, ",
    IF(DATEDIF(A2,B2,"ym")1,DATEDIF(A2,B2,"md")&" days",
    IF(DATEDIF(A2,B2,"md")=1,DATEDIF(A2,B2,"md")&" day",
    IF(DATEDIF(A2,B2,"md")<1,"")))

    1. My previous post got scrambled in translation, hope this one works:

      =
      IF(DATEDIF(A2,B2,"y")>1,DATEDIF(A2,B2,"y")&" years, ",
      IF(DATEDIF(A2,B2,"y")=1,DATEDIF(A2,B2,"y")&" year, ",
      IF(DATEDIF(A2,B2,"y")1,DATEDIF(A2,B2,"ym")&" months, ",
      IF(DATEDIF(A2,B2,"ym")=1,DATEDIF(A2,B2,"ym")&" month, ",
      IF(DATEDIF(A2,B2,"ym")1,DATEDIF(A2,B2,"md")&" days",
      IF(DATEDIF(A2,B2,"md")=1,DATEDIF(A2,B2,"md")&" day",
      IF(DATEDIF(A2,B2,"md")<1,"")))

  6. I have a project in which employee should be sent medical test for evey 3years(i.e.one day before completion of 3years) which formula should I use.ex...ajay medical test on 1/01/2014 next check up will be on 31/12/2017... Eagerly waiting for reply

    1. Hello,

      Please try the following formula:

      =TEXT(EDATE(A1,12*3)-1,"dd/mm/yyyy")

      Hope it will help you.

  7. I want to do the same but count the number of months going forward from say Aug to May in that format... Any thoughts?

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =DATEDIF(DATE(2016,8,31),DATE(2017,5,1),"M")

      Hope this will help you!

  8. Hi! Thanks for these instructions - the site/tutorials are great and I've learned a lot! I was wondering if you had any tips for me:
    I am trying to display the date, that is x number of days after Jan 1.

    I used:
    A1 =datedif("01/01/2017","11/27/2017", "d")
    to display how many days it has been since the start of the year.
    I am now trying to display the date 30 days prior.
    I was able to find the day number this year by:
    =datedif("1/1/17", A1, "d")- 30
    For instance, this equals 300. How do I display the 300th day of the year (i.e. October 27)?
    Thanks!

  9. How to calculate time in excel such as
    31/10/17 10:45:00

    01/11/17 02:15:21

    Please advise us above query

    1. Hello, Ahmer,

      Please try the following formula:

      =DAYS("01/11/17 02:15:21"-"31/10/17 10:45:00",0)*24 + HOUR("01/11/17 02:15:21"-"31/10/17 10:45:00") & " hours " & MINUTE("01/11/17 02:15:21"-"31/10/17 10:45:00") & " minutes " & SECOND("01/11/17 02:15:21"-"31/10/17 10:45:00") & " seconds"

      Hope it will help you.

  10. I am using the datedif function to calculate years of service for employment. I have that part down with the exception of employees that had a break in service. How do I add their previous years served before their break in service to their current years of service?

    1. Example:
      Current Date of Hire: 9/25/1999 to Present
      Current Time in Service: 18Y, 1M
      Previous Date of Hire: 5/23/1987
      Previous Termination Date:9/20/1991
      Previous Time in Service: 4Y, 3M

      How to I create a formula to add the current time in service along with the previous time in service?

  11. The datedif function no longer exists inf Office 2016. As usual, when the imbeciles at Microsoft "improve" a product, they remove useful functions.

    1. Hi Wayne,

      DATEDIF still works in Excel 2016. But it is undocumented, meaning you won't find it in the list of functions. To use DATEDIF in your formulas, you need to remember its syntax and type all the arguments manually.

  12. Hii

    I have needed a formulla, if a employee is joining date and leaving date would be >4 years and 6 months then he will get 50% bonus otherwise he will not eligible.
    please help me.

    1. I need exactly the same calculation. Have been using =datedif(a1,today()"m")>=6, however it is not the TODAY as reference but another date on the spreadsheet.
      so i have got date 1, date 2, and I need to highlight date 2 only if it is over 6 months older than date 1 if that makes sense.

  13. dateif doesn't exists

    1. Hi Alex,

      Dateif doesn't exist, but DateDif does :) However, DATEDIF in an undocumented function, which is why it does not appear in the list of formulas, so you will have to type a whole formula manually.

      1. YAS! thank you - dateif didn't work for me, but datedif did. thank you :-)

  14. Hi,
    I am using Excel 2010 and am trying to work out a formula to work out the difference between two dates
    Could you please help?
    Thank you

    1. =DATEDIF(B2,C2+1,"d")
      Please try this one. In B2 start date & in c2 end date.

      1. I see you add the +1 in the formula. I was wondering about this. I have accounted for this in the past, but everything shows that the number of actual days is apparently one less. Example: 01-NOV-2017 and 30-NOV-2017, no matter what function you use, the answer comes up 29 days instead of 30.

        I usually add the +1 at the end of the formula, but it should come out the same.

        It seems like the functions do not take into account the first day as if you really didn't meant to count it.

  15. These formulas are not working in my PC, I am using Excel 2013, and already checked all formulas are correct, surprised to see the error #value

    1. Check the date format. This has to be MM/DD/YYYY

  16. Hi,
    Need a help. I want to calculate the no. of days between two dates by considering that only "sunday" is weekly off & rest 6 days are working.

    Pl advice.

    1. I need a formula that gives me the number of days weeks and months between two dates. (Excluding weekends)
      I am trying to calculate the value of a rental tool per day week and month. Given the following rates : per day $10 per week $40 and month $150
      Thank you!

  17. I need between date different by this below format

    Start Date 19.02.1989
    End Date 01.02.2017

    1. By right click,format cells,custom,scroll and pick up mm/dd/yyyy and modify it as per ur requirement.

  18. Hi, I would like to know the Beginning Week Number and Ending Week Number of the month. Thank you.

    1. Unsure if your comment is aimed at me however I am using a start date in one column and an end date in another column. Dates starting are usually the 1st of the month and end dates are normally the last day of the month....

  19. I am not great at using Excel, so I apologize if this question seems a little basic, but I need help. I have a spreadsheet that has two columns of dates. I would like to use datedif to automatically calculate the difference between these sets of dates into a third column without having to type the formula into each cell. Is there a way to do this?

    1. Hello Torey,

      Of course, you don't have to type the formula in each cell manually. Just write the DATEDIF formula for the top cell using relative cell references (without the $ sign, like A1). Then double click a small square at the lower right-hand corner of the formula cell (it will change to the plus sign as soon as you hover over it), and the formula will copy across the entire column.

      You can find a handful of other ways to copy formulas in Excel here: How to copy a formula in Excel.

  20. If the second date is empty and I want the value to remain blank or say not complete how do I set that up. I am getting #value

    1. Hi Alex,

      You can use the IF function to check for blank cells, like this:

      =IF(AND(A1<>"", B1<>""), DATEDIF(A1,B1,"d"), "")

      or

      =IF(AND(A1<>"", B1<>""), DATEDIF(A1,B1,"d"), "not complete")

  21. Can you Please help me out with the following problem.I need to get two values from two different cells and combine it in one cell with certain preexisting characters.
    Ex: Cell A1 contains "Aakash" Cell A2 contains "D V". I need final expression in cell A3 as "UI-Aakash D V". Please let me know if it is possible and if it is kindly let me know how.

    1. Hello Aakash,

      You can concatenate the values in this way:
      ="UI-"&A1&" "&A2

      Or, use the CONCATENATE function:
      =CONCATENATE("UI-",A1," ",A2)

  22. I have a question... that kind of go along these lines...

    I have my formula: =DATEDIF(D3,TODAY(),"D")

    This is giving me the aging that I desire...

    The problem is, I want to be able to keep this aging... but I also want to stop the counter, when I plug in a completion date... of a given task...

    Any thoughts~

    1. Hi Shay,

      I think you can use a formula similar to this:
      =IF(E3<>"", DATEDIF(D3,E3,"d"), DATEDIF(D3,TODAY(),"d"))

      Where E3 is the completion date. If there is a date in E3, the formula calculates the difference between D3 and E3, otherwise between D3 and today's date.

  23. The DATEDIF function does not appear in the default installation of MS Office 2010. Is there a way to import this function, or update Excel 2010 to add this function?

    1. There is no way :( DATEDIF is one of few undocumented functions in Excel, and that is why it does not appear in the formula list.

  24. DATEIF calculates number of months between 1st Jan 2015 and 31 Dec 2015 as 11 months. How can I adjust the formulate to calculate the answer as 12 months. Thanking you very much for your assistance.

    1. Hi Colin,

      I had this issue also. Turns out the fix is quite simple, just add "+1" to the end of the formula. =DATEDIF(A1,B1,"m")+1

      For some reason the format of the cell seems to randomly amend to date, but you can simply amend back to number if this happens.

      Hope this helped!

      Dan

  25. I can't find DATEDIF function. It is only showing me DATE and DATEVALUE.

    1. Hi Tujju,

      DATEDIF is one of few undocumented functions in Excel, which don't appear either in the formula list, or when you start typing in the formula bar. To make a formula, you need to type all the arguments manually.

  26. Hello Svetlana...Good Morning....I'm trying to "correct" a formula that we need to calculate how to calculate date difference is days, months and years...so there are 2 dates start and end date. Currently the formula is =IF(A1="","", DATEDIF($A1,$B1,"md")&" days. "&DATEDIF($A1,$B1,"ym")&" m."&DATEDIF($A1,$B1,"y")&" y.")) Which is pretty good but when the data has a start date and "no" end date....I get a #NUM! error.
    For example: 1) Start 1/1/2016 End 2/15/2016 ... I get a 14 days. 1 m.0 y. it is good.
    2) Start 1/1/2016 End Blank .... I get a #NUM! error. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any support would be greatly appreciated.

    1. Hello Nani,

      You were looking at the right direction. You can add one more IF statement to your formula, which will calculate the difference between the start date (A1) and Today() if there's a date in A1 but B1 is empty:

      =IF(AND(A1<>"", B1=""), DATEDIF($A1,TODAY(),"md")&" days. "&DATEDIF($A1,TODAY(),"ym")&" m."&DATEDIF($A1,TODAY(),"y")&" y.", IF(A1="", "", DATEDIF($A1,$B1,"md")&" days. "&DATEDIF($A1,$B1,"ym")&" m."&DATEDIF($A1,$B1,"y")&" y."))

  27. Hi, I am trying to find out the difference between two dates. The dates refer to the expected end date and the actual end date, and I want to know how many have gone over time and how many days it has gone over. When I use DATEDIF above the dates where there would be a 'negative' result run as an error.

    1. Hi Natalie,

      If you are calculating the difference in days, then you can simply subtract one date from the other and get the result either as a positive or negative number.

      If you don't want negative numbers, then you can use a nested If formula similar to this:
      =IF(A2>B2, DATEDIF(B2,A2,"d"), DATEDIF(A2,B2,"d"))

  28. Using that formula from above : =DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days"
    Put 3/12/2015 in A1, and 1/11/2016 in A2. You'll get a result of 0 years, 9 months, 143 days. Why?
    Make A2 1/17/2016 and you get 0 years, 10 months, 5 days.

    Column A is formatted as Date, while the results column is General.
    Excel 2007 SP3. Win7 Pro (same result on Server 2008 R2)

    1. Hello, Robert,

      Looks like a kind of bug with Excel 2007. Sorry, we haven't been able to reproduce it in Excel 2016.

  29. Date of join Present Date Year Month Day
    22/01/2012 13/01/2016 3 11 135
    I am facing the problem in Day cell. Why it shows 135 days in day cell ? Please solve this problem. I am using Excel 2007.

  30. Hi,

    same question as KM, but on days, how can I get days in decimals, i.e., 1.47 days

    Thanks

    1. Hello, Chirag,

      Please use this formula:
      =ROUND(DATEDIF(A1,A2,"D") + (HOUR(A2-A1) / 24), 1) & " days"

      1. Same Question only for Months. How can I get the number of months in decimal. I.e. 11.65 months?

  31. Hi, I would like to have my result be in decimal form when calculating time between two dates. Example 3.7 years

    1. Hello, KM,

      Please use the formula below:
      =ROUND((DATEDIF(A1,A2,"M")/12), 1) & " years"

  32. hi may i know how to count a set of datas in weeks

  33. DATE DATE DAYS
    1/8/2016 4/7/2016 90.00 (=DATEDIF(C5,D5,"D")

    In the above case 2016 Leap year where DATEDIF missing 29th February, 2016 i.e. output result should be 91 days and not 90 days

    In case of manual dates calculation = 91 days is exact result.

    Can any one help DATEDIF (days/month/year) in caese leap year.

    1. Hello, Jagadeesh,

      If you enter the year of 2015, you'll get 89, for 2016 you'll get 90. Hope this helps.

      1. In my excel worksheet, I entered:
        1/1/2016 12/31/2016 (=DATEDIF(C5,D5,"D") and it came up with 365 rather than 366. Please advise how to get it to count Feb 29,2016.

  34. how to change date format 26/10/2015 in to 26-Oct-2015 using the excel formula.. kindly send me ans

  35. Hello Svetlana....Good afternoon....I'm trying to "correct" a formula that we need to calculate how many months in a year an employee has worked...so there are multiple start and end dates. Currently the formula is =(DAYS360(D2,E2)+DAYS360(F2,G2)+DAYS360(H2,I2)+ DAYS360(J2,K2)+DAYS360(L2,M2)+DAYS360(N2,O2))/30 which is pretty good except when the employee has a start date and "no" end date....I get a negative number. For instance: Start 1/5/2015 End 3/23/2015 Start 3/23/2015 End 8/28/2015 Start 10/5/2015...I get a -1381.4. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any assistance would be greatly appreciated.

    1. Hello, Anita,

      You can use the IF function for the end date function parameter. For example IF(ISBLANK(D2); TODAY(); D2)

  36. Good afternoon....I'm trying to "correct" a formula that we need to calculate how many months in a year an employee has worked...so there are multiple start and end dates. Currently the formula is =(DAYS360(D2,E2)+DAYS360(F2,G2)+DAYS360(H2,I2)+ DAYS360(J2,K2)+DAYS360(L2,M2)+DAYS360(N2,O2))/30 which is pretty good except when the employee has a start date and "no" end date....I get a negative number. For instance: Start 1/5/2015 End 3/23/2015 Start 3/23/2015 End 8/28/2015 Start 10/5/2015...I get a -1381.4. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any assistance would be greatly appreciated.

    1. Hello, Anita,

      You can use the IF function for the end date function parameter. For example IF(ISBLANK(D2); TODAY(); D2)

  37. I am trying to find a formula that will turn a certificate name cell green if a date is less than 9 months old, yellow if the date is between 9 and 10 months old, orange if it is between 10 and 11 months old and red if it is over 11 months old. this is for one year expiration but I want to apply it to 2, 3 and 4 year expiration dates as well.

  38. Hi there,

    I've been trying to find a formula which calculates the number of weeks and days from 2 dates.

    Can you help?

    1. Hello Adam,

      Please use
      =IF(INT((A2-A1)/7)>0,INT((A2-A1)/7)&" week(s) ","")&IF(MOD(A2-A1,7)>0,MOD(A2-A1,7)&" day(s)","")

      A1 - start date, A2 - end date

  39. How would you take the todays date in A1 subtract that from all dates posted in Column D and post the days till in Column G?

    1. Hello Chadd,

      Supposing that row 2 is the first row with dates, enter the following formula in G2, and then copy it down to other cells in column G:
      =$D2-$A$1

  40. Hi Svetlana,

    I used the formula "Datedif" both for Month and year, it's missed one Month or one Year. Eg. 01/Jan/2015 and 31/Dec/2015 the logical is 12 Months but the answer of the formula is 11 Months (Wrong).

    How can this be done?

    Thanks.

    Regards
    Abby

    1. did you find the an answer , I am also facing the same issue. thanks

      1. Hi abby & Svetlana,

        did you get the answer, i also face the same issue.
        start date is 1/7/15 and end date is 30/6/15.
        the formula answer only 11 month. It suppose to be count as 12 month.

        could you please share idea to counter the problem.

        thank you.

        1. I was facing the same problem of getting the correct month difference between 2 dates. For example:

          30/09/2016 - 31/10/2016 - Datedif given me 1 month difference, but then
          31/10/2016 - 30/11/2016 - Datedif given me 0 month differece.

          This showed inconsistency of computation.

  41. Hi Svetlana,

    Your improved formula for Linday is great comment-162067.xlsx. However, is it possible to exclude the holiday/s in a particular month/year?

    Thanks in advance.

    1. Hi Gregg,

      you can use the =NETWORKDAYS.INTL() formula to get the number of days and also exclude the holidays.

      Regards.

    2. Hi Gregg,

      Regrettably, we do not see an obvious way to do this in a formula, most likely a VBA script would be needed.

  42. Hi Svetlana,

    I was looking for a similar Excel formula as Sam and found that this one will only work if the date range are in the same year.

    How would the formula change if the dates were in different years? For example if I need the days totaled for April 2015, May 2015, and June 2015 from the date range of 23-Sep-2014 to 4-Dec-2015?

    Would this be possible to do?

    Thanks for your help!

    1. Hi Lindsay,

      We have improved the formula to work with different years. Here's is an example.

  43. Hi, I'm trying to set up a table where the difference between dates is needed, however I need to be able to break this down into months (the table to heave headers for the months). For example, if we have a start date of 01/04/14 and an end date of 07/05/14, is there a formula I can use which will automatically give me 30 days in one cell for April, and 7 Days in another cell for May?

    1. Hi Sam,

      This is feasible, but the formula is not so easy to explain :) We've made an example for you and you can download it here.

      1. Brilliant, thank you so much for your help!

  44. Let me know if you receive it love. Thank you

    1. Louie,

      You could use the following DATEDIF function for column F:
      =IF($E5<>"", DATEDIF($E5, $F$3, "d"), "-")

  45. If I can send an attachment I mean. Thank you

    1. Louie,

      You can attach a file when posting a question on our forum. You just have to register (create a user account), then click the blue "Start New Topic" button in the upper right corner, and then click the Attach Files button in the lower left corner.

  46. Hi. i just wondering if I cannot send an attachment with regards to problem in calculating the days( dates). So that I can show you the spreadsheet.
    They asking me to calculates the number days since the student say the exam. If the student has not yet sat the exam, instruct the formula to display a hyphen.
    The formula that used is DATEDIF but my instructor said its wrong. I hope you can help me. Thank you. > Louie

    1. Hi Louie,

      We are by all means avoiding posting our email address anywhere because of spammers. So, can you please post the same question on our forum and attach your workbook. Our support team will do their best to help.

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 :)