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

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

  2. Its Good
    Thank you !

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  21. 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./.....

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

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

  24. =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

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

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

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

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

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

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

  31. I have a particular date for a particular task and if the date has past 2 days from the bench mark date then how can I put a formula to highlight that this particular task has been already passed 2 days so that I can chase for the same.

  32. I need my code to hide a row once the date entered is 30 days past current date. Also all the cells are not filled, the date will be enter later I don't want too see value errors in the blanks.

    How can this be done?

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

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

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

  36. Hi

    Please give the formula in excel how do I convert a number in different row year month and day, tried my best but failed to get the solution. Suppose give me the solution below -

    Day Year Month Day
    400 ? ? ?

    Regards
    Sazedul

  37. 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"), "-")

  38. Hi Svetlana, I already posted my topic : Essential Function.
    Hope you can help me. Thank you again > Louie

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

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



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