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

  1. Hi,

    Suppose we need to calculate Date of Retirement using available Date of Births of Employees. How to write a single formula for calculating Retirement Date which should be calculated depending following three conditions - If Public Sector Employee then Retirement Age should be 60 yrs, If Private Sector Employee the Retire Age should be 58 yrs and for Pensioners should be 70 yrs

    Thanks

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

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

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

  5. Hi
    I would like to know some questions the following case.?
    I have start date and end date for some contracts.
    I used these formula.
    =DATEDIF("Start Date", "End Date", "d")

    I also need to know remaining date (start from open the file date)
    So, I used these formula
    =DATEDIF(TODAY(), "5/20/2015", "d")

    But, Contract start date aren't start when i open the day. So.. it is problem for me.Please tell me how to solve these formula?
    i hope you answer to me.
    Thanks & Best Regards,
    Wint Wah

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

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

  8. Great instructions! on negative dates code for between two dates! Best on the web.:) Thank you!

  9. Hi I am trying to automatically get column names in excel basis start date and duration as input. For example if I fill start date as 1-Jan-16 and Duration as 3 years or 36months it should automatically populate columns like Jan-16, Feb-16, Mar-16 .......Dec-18.

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

  11. how to find the difference in dates in months so that lets say employees joining before 15th of the month are considered to be working for 1 extra month and employees joining after 15th are not considered? Is it feasible?

  12. How to calculate number of days for the below dates
    09/04/2017 12:50 20/04/2017 11:55

    and as soon as the date cross the time of the start date, one day should be added, how to do it?

  13. Dear,

    Really, it is very help full because sometimes if we don't get in touch with these formulation, can be washed from brain.

  14. Hello Svetlana, Good day

    I'm trying to calculate date difference is days, hours and minutes so there are 2 dates start and end date. Start Date (Date Value) and End Date (Blank or have formula). Any support would be greatly appreciated.

  15. How Two or Five Period of Years, Month, Day Example:
    01 September 1994 31 October 1994 0Years, 1Months, 30days
    04 September 1995 30 November 1995 0Years, 2Months, 26days
    01 December 1995 30 May 1996 0Years, 5Months, 29days

    Total: ______ _______ _____

  16. hello
    I'm trying to figure out, how to calculate no. days between two dates for a particular product which is repeating at different dates.

    plz help me out..
    thanks.

  17. Hi, I'm trying to find a formula calculating days that has passed since..
    For instance, how many days have passed (aged) since date of entry. This will be updated based on an input calendar date.

    Is this possible?

  18. Hello,
    I need to create an elapsed days formula from a start date to today's date for submittals. When the submittal is returned I would like the formula to show elapsed days from the start date to the end date (received) and not today's date.
    Kind regards.

  19. Hi,

    How can I calculate together month and year in excel macro?.

    e.g.
    01-Jan-2017 (Textbox 1)
    01-Feb-2017 (TextBox 2)
    Result: 0.1 (Textbox 3)

    Thank you in advance.

  20. Hi, good day.

    I have a problem to calculate my rental in different period and rate.
    Eg.
    1st - 7th year : 14.09.09 - 13.09.16
    8th - 10th year: 14.09.16 - 13.09.19

    1st - 7th year : $1,000
    8th - 10th year: $750

    1st - 7th year : Splitting percentage 20%
    8th - 10th year: Splitting percentage 50%

    How to converting the above in If formula in excel?

  21. Hi, good day.

    I need help in converting the following if-statements in Excel formula:

    1. If A1 and B1(there are formulas in the column that make of them look blank, although the actual cell is not blank) are blank, then C1 is "";
    2. If A1 is not blank and B1(there are formulas in the column that make of them look blank, although the actual cell is not blank) is blank, then C1 is "Pending";
    3. If A1 and B1 are not blank, then C1 is "Completed".

    What formula can I use?

    Thank you in advance.

  22. Hi Deni,

    Try this:

    =DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months, " & DATEDIF(A1,TODAY(),"md") & " Days"

  23. Hi Rach,

    Try this:

    =IF(A1"",DATEDIF(A1,TODAY(),"d"),"")

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

  25. I have just used the DATEDIF formula for Years, Months and Days. I would now like to round these figures up i.e. 14 years 30 Days = 14 years 1 Month/8 years 4 months and 30 days = 8 years and 5 months. Is there an easy way to do that or should I just revert to the DATEDIF formula for Years and Months? Many thanks.

  26. Hello there, I have a question. I have tried to read all posts but it's hard to know which formula I need. I have a birthdate and as of today I can figure out the exact age by year, month and day. Now, I need to know in 3 weeks from now by a specific date how old the pet will be. For instance, the birthday is 11/24/06 so I know from today how old the pet is. But now I need to know for future dates of 02/16/17, 03/15/17 and 04/20/17 exactly how old on those dates the pet will be. I have lined up the dates in the row but I keep getting errors when I try and create a formula from the birthdate...help?

  27. Hi requested support. i want to calculate the number of days after close of month. if their is any support methodology for this query. Please support.

  28. When I try and perform a sort based on results from this formula, it does not sort correctly. Same thing happens if I use a simple =B2-A2 equation. It looks like Excel is returning a number (as opposed to a date). Any clue why it won't sort? Any ideas for a solution?? :-)

    Thanks in advance!!!!

  29. D1 to D100 is the due date and I need column E to give the number of days past due date - PLEASE HELP!!

  30. D1 to D100 is the due date and I need column E to give the number of says past due date - PLEASE HELP!!

  31. Hi there, really struggling with this...
    I am trying to determine the number of nights per week that someone has stayed in accommodation based on their check in (Ci) and check out (Co) dates compared to the week start (Ws) date and week end (We) date.
    Brackets are the named columns / rows.
    Would really appreciate your help on this as would save a lot of manual entry time!
    Many thanks
    Matt

  32. Hi, I am trying to figure the number of years worked between hire date and the end of our next fiscal year. When I enter the formula it gives me an answer of 1/19/1900. This is the formula I am using =DATEDIF(C2, D2, "Y") C2 is the date of hire and D2 is 06/30/2018. Can someone please help me fix this?

  33. Hi,

    I'm currently making a company ID with an expiration date. In this case there are 2 basis for expiration date. 1 is passport expiration and the other one is visa expiration. I need to see which of the 2 expires first so that the ID validity will be based on whatever dates comes first. hoping for you response. Thanks!

  34. How to get the difference between Date which contains time as well. I am looking into find the "Days difference by comparing two cells"

    Cell A: 12-13-2016 19:13
    Cell B: Current Date

    In Column C: I want to know how old is the ticket pending.

  35. ABA CHAVHAN Nov-14
    ABA CHAVHAN Dec-14
    ABA CHAVHAN Jan-15
    ABA CHAVHAN Feb-15
    ABA CHAVHAN Mar-15
    ABA CHAVHAN Apr-15
    ABDULRAHIM S KHAN Dec-14
    ABDULRAHIM S KHAN Jan-15
    ABDULRAHIM S KHAN Feb-15
    ABDULRAHIM S KHAN Mar-15
    ABDULRAHIM S KHAN Apr-15
    ABDULRAHIM S KHAN May-15
    AJAY APPA GAIKWAD Nov-14
    AJAY APPA GAIKWAD Dec-14
    AJAY GAGAT Nov-14
    AJAY GAGAT Dec-14
    AJAY GAGAT Jan-15
    AJAY GAGAT Feb-15
    in this case which formula calculating month

  36. Hi,

    I need this code to exclude duplicates from the previous 10 days inclusive.

    =IF(COUNTIFS(A$2:A$20002,A2,B$2:B$20002,B2)>1,"1","")

    I'm counting duplicate errors but I takes circa 10 days to close an error so I need to exclude those.

    Thanks,

    Niall

  37. If i give those Date and time, Then what difference between those day's and time it is ?

    Give an Example:

    Two date with time....
    Start date & time: 11:39 PM 12/2/2016
    end date & time: 01:39 AM 12/3/2016

    Then, How is differentiating two identities in excel.

    I want just actual time..........

    Please help me........

  38. Very good formula, thanks for this

  39. Hi!
    My question: first date = 12.02.2000, second date = 1.5.2012. How to get in excel 2007 or 2013 the number of days of leap years ?
    (The number of these days should be 324+366+366+121 = 1177 days).
    Thanks to the possible response.

  40. Thanks, it was really helpful!!!

  41. Hi

    I want to know the formula which will just give year and month where month is not in 2 decimal. Here is the formula which I have used =DATEDIF(E7,TODAY(),"Y")& "."&" "&DATEDIF(E7,TODAY(),"YM")

    and result is 0.10 which is 10 months, with this result I am not able to filter the file in chronological order.

    Is there a way to resolve this problem.

  42. Hi,

    I want to know which formula I have to use if I want to calculate no. of days before a specific date, and total no. of days after or from specific date

    for ex: if am calculating number of sick leave days for employee per year and different period of sick leave given.

    Regards

  43. How to convert "2 years 10 months" into number of days

  44. Hi
    Svetlana

    Thank you, but I do not have good English helped me to 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"

    Thank you,Thank you

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

  46. Hi,

    I am trying to find the chronological age in years and months between a birthday and a testing date.

    For example:
    Birth Date: 3-3-2001
    Test Date: 6-29-2007
    Then the age would be: 6 years 3 months

    Birth Date: 8-2-1999
    Test Date: 5-8-2006
    Then the age would be: 7 years 9 months

    Birth Date: 11-24-1985
    Test Date: 8-13-2006
    Then the age would be: 21 years 8 months

    Is this possible? Thanks so much!

  47. Hello Svetlana,
    Please could you assist me in my below calculation because I am struggling 20 hours and cannot get it using many formulas.
    I have this formula : Cell D44 03:15:30 (where dd:hh:mm) - Cell D45 00:10:52 (where dd:hh:mm). I want to get the result in the cell D46, in case that D45 is greater then appears ####### I tried many solution but they didn't work.
    Additional, when I form the cell D46 with different colour based on the result then when I close the Worksheet and re-open it again the colour hasn't remained as I formed. I don't want to use the form 1904, I used it but the dates were not those I wanted to get. I used Excel 2011 in Mac computer.
    Thanks a lot in advance.

  48. Hello Svetlana,

    Can you Please help me out with this: I need to calculate number of working years, months and days between two dates with break.
    EX: If few employee start working at one date and working certain period then left company and after few years comeback. With DATEDIF function using SUM function getting wrong result.
    Please let me know if it is possible and if it is kindly let me know how.

  49. I have a spreadsheet that provides the date of when an business item is received for processing. And a second column for a date when the item is submitted for processing. I would like a third column that highlights each item where the difference between receipt and submission is greater than 3 working days. Thank you for your assistance.

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

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