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 8. Total comments: 298

  1. 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.

  2. THANK YOU !

  3. thank you. very helpful.

  4. This is epic!
    Working with this one:
    How to calculate date difference is days, months and years

    Is there a way to calculate the max, min, and average time?

    Thank you so much!

  5. I have hit a brick wall I need to calculate the difference between the current date(day) and a past fixed date or a future fixed date so as to calculate months post contract expiry date (negative) or how many months to contract expiration date(positive)

    Reply

  6. I have hit a brick wall I need to calculate the difference between the current date(day) and a past fixed date or a future fixed date so as to calculate moths post contract expiry date (negative) or how many moths to contract expiration date(positive)

  7. Hi,

    I get an error message each time I try to use datedif function. The error message says: "The formula you typed contains an error"

    Can anyone help me with this?

    Alfred

  8. 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

  9. 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.

  10. I'm trying to calculate the amount of time in years someone has worked here but that number needs to change based on the date the excel sheet is opened. So if I open this sheet next month, the numbers will change to show the time worked calculated from their start date to whatever date I open the file next month. Likewise, if I open the file 6 months from now, the number of years worked will change accordingly to include the past 6 months. Not sure I'm explaining this well but I hope you understand.
    Thanks in advance for your help.

  11. I'm using the datedif formula to initially calculate days and then weeks (DATEDIF(X11,Y11,"d")/7. I calculated weeks for the entire year, 1/1/16-12/31/16, and came up with 52.14. However, I need to do it by month - and when I come up with a total number of weeks, using the same formula, the total for the year is 50.57 weeks. I'm off by 1.57 weeks. What am I doing wrong? This exercise is being used to determine temp costs.

    Many thanks

  12. Hi - can anyone help me with a formula,
    I want to calculate the number of days between Today and Column M. I want to display positive and negative numbers but sometimes there is no date in column M.
    If I use =TODAY()-M1, I get a #VALUE error if no date
    If I use ==DATEDIF(Today(), B2, "d"), I get a #NUM error for negative output.

    Please help!

  13. HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)

  14. 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."))

  15. Parameters:
    Item Start Date End Date Value
    A 01-Apr-11 02-Feb-12 2
    A 03-Feb-12 01-Mar-12 3
    A 02-Mar-12 31-Dec-13 4
    A 01-Jan-14 31-Jan-14 5
    B 09-Jan-13 04-Apr-14 6
    B 05-Apr-14 07-Feb-15 7
    B 08-Feb-15 01-May-16 8
    B 02-May-16 01-Jun-16 9

    Item Date Value
    A 30-Apr-12
    A 05-Feb-12
    A 30-Jan-14
    B 07-Apr-14
    B 20-Feb-15

    Please help for for the above value column based on provided parameters.

  16. Hi guys,

    I am working in big excel, it has more than 2M records, I am using PowerPivot. I am trying to aggregate the data by day. I have a lot of transaction in a day, I want to group that. I tried to use the following DAX but it doesn't seem to work...I am new for this...any tips?

    =COUNT([column name],CALENDAR(2016,1,31.2016,1,31))

  17. Guys, can anyone help me with this.

    i have 2 dates Call confirmed - 01/02/2016 11:47 & Dispatched - 05/02/2016 13:50. i want to calculate how many days hours and minutes. the result should be in this format 0 days 0 hours 0 minutes. please help with this.

  18. Hi friends,

    I need to find age from TODAY() but date format is below in a cell(Serial no. Date Time) & (Age) and there needs to compare with current date and cell date into age.
    =============================================
    ----------A-COLUMN-------------!--B-COLUMN--!
    =============================================
    Serial no. Date Time ! Age !
    8505642244 29-02-2016 11:30AM ! ? !
    8505642249 26-02-2016 11:30AM ! ? !
    8505642444 20-02-2016 11:30AM ! ? !
    =============================================

    Can it possible? if yes, please share.

    Thanks!

  19. Hi friends,

    I want to find age from TODAY() but date format is below in a cell
    ----------A--------------------!
    Serial no. Date Time Age
    8505642244 29-02-2016 11:30AM! ?
    8505642249 26-02-2016 11:30AM ?
    8505642444 20-02-2016 11:30AM ?

  20. Hi there,

    I was wondering if there is a way to sort out financial years between 2 dates. EG 01/01/2013 to 19/10/2015. We know these dates span 4 financial years. Is there a way to allow excel to sort these dates so they appears like the below:

    01/01/2013 to 30/06/2013
    01/07/2013 to 30/06/2014
    01/07/2014 to 30/06/2015
    01/07/2015 to 19/10/2015

    Thanks!

  21. Hi I am using an incident/accident database.
    I have the date of the accident in C1
    The date returned to work L1
    I want to count the number of weekdays missing however I want to count the days missing in the month that the accident happened and days carried over to the next month.

    Eg if accident happened 29/01/2016 returned to work 05/02/2016
    how may weekdays were missing in January?
    how many weekdays were missing in February?

    Thanks and hope this makes sense

  22. Its Good
    Thank you !

  23. i want a formula to calculate interset on quarterly basis from the date of investment

  24. Need to count how many days in a contract will occur during a specific period of time. Example: contract runs 9/2/2014 - 8/31/2015 during the period of 9/1/2014 - 9/28/2014.

  25. Hi there
    I need assistance with a formula. Setting up the excel file to calculate the lead-time from when the product was ordered to when it was received, however the cell must over-ride so that the calculated figure is always divisible by 7 eg. actual lead-time calculated is 23 days, however new rule is to formulate it to the 7 day = 21 days

  26. Hi Cheysheva

    wonder if you can guide me how to count date and time different for working hours only? (0900-1700)

    your help would be greatly appreciated...

  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. I need to calculate time difference for 500 different dates in column B and column A in year decimal point. Is there a formula that will calculate all 500 time difference and present it in column C?

  29. Hello,

    I have the following situation: Sheet 1 contains a list of articles (code and name), with Start delivery date, End delivery date, and a promotional price which applies only between those dates.
    Sheet 2: a database with all the articles delivered, date of delivery and delivery price.

    I need the following: in sheet 2, I need to bring the promo price for a certain article from sheet 1, if the article was delivered between the dates (start and end delivery date).

    Please help!

  30. Hi Maria,
    I try to calculate for 12 month the annual leave for worker who work in my company example:
    Januar, februar, March,April and worker used in januar with date 4 day in march used 6 days i lile to know total days with date start and end date diference can you help me please.

    Agim

  31. 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.

  32. 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.

  33. very useful....and very helpful for all champs...

  34. 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?

  35. 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"

  36. Hi
    May i know how to calculate in date two between date,month and year ?Please give me example and formula.I wish u all the best.

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

  38. 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.

  39. This guidance was superb. But if I consider all months of 30 days (360 days for year) how can I calculate difference between two dates in days / months/ years etc?

  40. Or if like me you would like to see a dynamic formula that takes into account the differnce in multiple months for example then the formula could be adapted as follows:

    =IF(DATEDIF(A2,B2,"y")=0,"",DATEDIF(A2,B2,"y")&" years ")&IF(DATEDIF(A2,B2,"ym")=0,"",DATEDIF(A2,B2,"ym")&IF(OR(DATEDIF(A2,B2,"ym")>1)," months "," month ")&IF(DATEDIF(A2,B2,"md")=0,"",DATEDIF(A2,B2,"md")&" days"))

  41. i am looking to find the formula of leave management system in excel. i have pick Date calender from developer option and drop in to 2 cells like C20 start date of leave and G20 End date of Leave. In other cell i have 30 casual leaves in 1 year now i am coming to the point when i will select start date of leave and end day of Leave the Automatically Cut leaves from the other 30 casual leave is it possible./.....

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

  43. 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)

  44. =DATEDIF(K4,J4,"D") IS NOT WORKING IN MY EXEL CAN U HELP ME
    DATE FORMAT IS SHOWN UNDER
    14/05/2015 30/05/2015

  45. 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)

  46. 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.

  47. 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

  48. 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

  49. sir,any formula u have which calculate days among four dates.

  50. 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.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)