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

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

  2. Hi Deni,

    Try this:

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

  3. Hi Rach,

    Try this:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  18. Very good formula, thanks for this

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

  20. Thanks, it was really helpful!!!

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

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

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

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

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

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

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

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

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

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

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

  32. I'm trying to calculate the number of days a task is overdue. C2 contains the date the assignment is due and d2 is the date the assignment was completed. Example:

    Assignment Due Date Assignment Completion Date Days Overdue
    5/9/2016 5/2/2016 0
    5/10/2016 5/13/2016 3

    I need to be able to calculate the number of days overdue. What formula do I use? I know I can't use the DATEIF formula. Thank you for your help.

  33. I have simple formula to calculate number of days between dates =DAYS(G266,C266). If there is no date yet in G column the result in the H column with the formula is -42412 or #VALUE!. What formula in the H column cell blank when there is no date yet entered?
    Thanks!

  34. HI
    IN CONDITIONAL PARAMETER

    01 APRIL 2015 TO 30 JUNE 2015 = IS 1ST QTR
    01 JULY 2015 TO 30 SEPTEMBER 2015 = IS 2ND QTR

    01 OCTOBER 2015 TO 30 DECEMBER 2015 = IS 3RD QTR

    01 JANUARY 2015 TO 30 MARCH 2015 = IS 4TH QTR

    IF WE PUT THE DATE AS WE DESIRE IT WILL AUTOMETIC PRINT/DISPLAY IT QTR

  35. Hi Svetlana

    Could you please help me with the formula.
    I have used data validation. All I want is if it says says. I want a date to be populated after 7 business days. If no I want the date after 5 business days to be populated in a cell.

  36. how are you,may you pliz send me a worked downloadable example on hw to calculate days between two dates.

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

  38. Hi Svetlana,

    kindly help, i put different date in one column i need the date to date between days
    For example Column A i give different date 1 to 10 cell now i need column B the different date to date days
    Column A Column B
    11-May-16
    13-May-16 ?days
    17-May-16 ?days
    1-Jun-16 ?days

  39. I am using this formula and the result is not correct. For example, I'm calculating the number of months between 3/26/2013 - 1/30-2016, which is logically 2.10. I use the formula =ROUND((DATEDIF(G2,J2,"M")/12), 1)and the result is 2.8. The cell G2 contains the date 3/26/2013 and J2 contains the date 1/30/2016. Where did I go wrong?

  40. 5/25/2016 -8/31/2017 if between these two dates i want to find no of days in may then june and so respectively how do i do that. for example in may the number of days are 5 . i want the formula for this.

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

  42. THANK YOU !

  43. thank you. very helpful.

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

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

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

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

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

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

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

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