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

  1. Thanks Svetlana, for your nice teaching.

  2. How to create a formula ( using IF) which can select number of days within the particular month from the two column dates which will vary to a year - as below ;

    Start * End * no. of days in Jan * no. of days in Feb * no. of days in March* no.of in Aprl
    26-Jan-21* 11-Feb-21- ..................... ............... .... ............... ..........
    3-Feb-21* 28-Feb-21 ...................... ....................... ............... ...........
    5-Feb-21* 25-April-21 ...................... ....................... ............... ..............
    20-March-21*2-June-21 ...................... ...................... ................ ...............
    25-March-21*28-July-21 ........................ ....................... ................ ...............

  3. I want difference between two days should be divided into months & days
    e.g.
    01-MAR-2020 - 30-Apr-20
    basis datedif formula the result is coming as "1" months where as it should come as "2" months

    Please help

  4. In the following example 01/01/2020 - 31/08/2020 - DATEDIF returns 7 months instead of 8. Does anyone have a solution to this problem?

    1. Hello!
      DATEDIF returns the number of full months between dates. In your example, the difference is 7 months and 30 days. The second date is 31/08/2020 00:00:00. That is, this day has not begun.

  5. Hello. Can you please help me figure out how to count how many times multiple installations date ranges occur each in week of each year between two date columns for each installer?

    I'm trying to count how many, let say "installs," occur per week between the start date and end date columns.

    There is more than one row with start dates and end dates that overlap, and I'm trying to break it down by how many are overlapping in each week.
    Column B & C are the start and end of the install. E and F are just part of my model.

    For example, someone may have 44 installs per year, but how many are occurring each week at the same time.
    I'm trying to show how many installs occur/overlap to the right of columns E and F via column G.

    See tab SE Grid (DEB). I'm pretty sure the numbers are correct, but you can double-check.
    Link to the spreadsheet with manual calculations: https://docs.google.com/spreadsheets/d/1UGQxHzjHi4UWMcT_4shbLt45n5KW2jzAjUe5Evz7JRg/edit?usp=sharing

    I think the spreadsheet link below is close, but I see some discrepancies. Link to the spreadsheet with the formulas tried below: https://docs.google.com/spreadsheets/d/1krnLiVUTfXWIWh0PTVXqK9Zpy5lNegYHoUWGMTruI88/edit?usp=sharing 

    Here are some formulas I have tried:

    1. =SUMIFS($H$3:$H$44,$G$3:$G$44, >=K3&)+SUMIFS($H$3:$H$44,$G$3:$G$44, "&K2,$A$2:$A$217,$H$2:$H$217,"=K3"},0))*ISNUMBER(MATCH($H$3:$H$44, {"=K4),--($G$3:$G$44"&WEEKNUM(N3),$L$3:$L$44,"="&D1,F5:F18,"<="&D2)

  6. Hi
    Presuming i have 2 dates 1 Apr 2019 in (C3) and 31 Mar 2020 in (D3). I want to calculate the duration of of service in Years, Months and Years. I used the following formula :
    =DATEDIF(C3,D3,"y")& " Years, "&DATEDIF(C3,D3,"YM")&" Months and " &DATEDIF(C3,D3,"md")& " days"
    The answer i got was (2 years 11 months and 30 days) I want the date to show as (3 years, 0 months and 0 Days)
    How do i go around this?

    Thank you

    Tessie

    1. Hello Tessie!
      I am not sure if this is logical, but you can use the formula

      =IF(DATEDIF(C3,D3,"md") < 30, DATEDIF(C3,D3,"Y")& " Years, "&DATEDIF(C3,D3,"YM")&" Months and " &DATEDIF(C3,D3,"md")& " days",DATEDIF(C3,D3+1,"Y")& " Years, "&DATEDIF(C3,D3+1,"YM")&" Months and " &DATEDIF(C3,D3+1,"md")& " days")

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. I have an csv data I have to calculate the days between the order date and the ship date.
        i have used some formulas but seem not to be working for me.
        please help
        thank you

        1. Hello!
          When importing data from CSV, the date is recorded as text. I can't see your data, so I recommend using the instructions above.

  7. Hy if i have two dates i.e
    01/04/2020 to 10/08/2020 then how to bifurcate difference between two dates into different months i.e in April month-30 days, May month 31days, June 30 days , July 31 days, Aug 10 days

    1. Hello Dishant!
      I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. In that case I will try to help you.

  8. I used the DATEDIF function to calculate the difference between 2 dates but when the value is negative it displays error. How can I correct it?

    1. Hello Aminu!
      Please try the following formula:

      =IFERROR(DATEDIF(A1,A2,"d"), DATEDIF(A2,A1,"d"))

      Hope you’ll find this information helpful.

      1. Same issue, but cannot seem to make the formula work. My original formula is =DATEDIF(C3,D3, "d") & " days"
        And it works if C3 is lets say 01May2023 and D3 is 04 May 2023.
        But if the date in C3 is 04May2023 and D3 is 01May2023 it should display -3 days but I get an error.

          1. Hi, please help as I found negative value as the result of the DATEDIF function as follows :

            Presuming I have 2 dates 31 May 2014 in (A1) and 1 Mar 2015 in (B1), I use the function : =DATEDIF(A1,B1,"md") and why it returns -2 as the result ?

            Then I try to use longer function :
            =DATEDIF(A1, B1, "y") &" years, "&DATEDIF(A1, B1, "ym") &" months, " &DATEDIF(A1, B1, "md") &" days"
            And why it returns :

            0 years, 9 months, -2 days ?

            Is the "-2" days value considered an excel calculation error as there should be no negative value for date difference ?

  9. hello
    please help
    i create excel in a sheet
    what i need is to some all items with the same date alone example whole 01/january alone and 02 january alone and finally add up between 01/january and 02/january together in the month of january

  10. Hi,
    I have 24 columns with Jan' 20 to Dec' 21 and having some values in rows now I wish to transfer values to another sheet in columns (Jan'20 to Dec'21) by adding days different for all rows like 1, 10, 15, 30, 45, 60, 75, 90 etc.
    For Eg. If I have 20 in Jan'20 and need to add 45 days in Jan' 20 then another sheet should have 20 in Feb' 20 or I have to add 90 days then 20 should go to Mar' 20 or Apr' 20.

    Please advise.

  11. Hi guys,
    how to i calculate between two years

    example: 2years 4months and 5years 3 months

  12. Please send the the formula for following conditions
    If the date is between 2 Jan 2019 and 1 July 2019 the result would be 1 Jan 2019

  13. Hi, I need a help to find Time Different of Two Dates with time but its should be with in two other Date and Times. Eg. END Time : 12/18/2019 23:16 - Start Time : 12/18/2019 21:16 but it would in bracket of available Time 12/18/2019 22:16
    and 12/18/2019 22:26 . Ans: 0:10:00 ; How can I reach ?

  14. In datedif formula the days should not come as "30"
    eg.486 days = 1 year 3 month 30 days (in datedif formaula)
    but we want the answer is 1 year 4 month .

  15. if i have a date range 10/01/2019 - 11/19/2019, what formula will calculate the number of days in the first month only? It would also need to consider if the date range is 10/01/2019 - 10/05/2019.
    Many thanks,
    B

  16. Someone please can guide me how i can extract required text from below data as i need Model name i.e. Smsng, ID Iphone x, Code125, EMEI, EMI, ID.

    Please guide.

    Code125 EMEI2546 Alb Dist. Iphone x user Alph.
    ID36520 2564MEI Smp. Lng Smsng met 2456
    ID36520 2564MEI Colt. Conc. local mkv 365
    ID36520 EMEI2546 Alb Dist. Smsng mkl 25
    Code125 2564MEI Smp. Conc. Iphone x user 14
    Code125 2564MEI Smp. Lng local met 896
    ID36520 2564MEI Alb Conc. Iphone x mkv 125
    Code125 2564MEI Smp. Dist. Smsng mkl 35
    ID36520 EMEI2546 Alb Lng local user 46
    ID36520 2564MEI Smp. Dist. Smsng met 125
    Code125 EMEI2546 Colt. Mnt. Iphone x mkv 400
    Code125 2564MEI Alb Lng local user 600
    Code125 EMEI2546 Alb Dist. Iphone x met 1915
    ID36520 2564MEI Con Lng Smsng user 16

  17. If i have start date & end date available in column so mentioned formula is use full to calculate the days, but if any column only start date mention and end date is not available & i want to know how many days have passed from start date. any formula where we can use both formula's to calculate the same.
    Start Date & date : =DATEDIF(H2,I2,"D")
    only Start date : =TODAY()-H2

    How we can use both formulas

    1. Hello,
      Did you recieve a response to this query as I am looking for a formula to do the exact same?

  18. Dear,
    I have same question like Rab Nawaz.
    I have to count different data, like as shown on the right side here (only count Years, Months and days)
    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

    Thank You beforehand.

  19. Dear,
    I have same question like Rab nawaz.
    I have count different data like as
    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

  20. 01/07/2019 20:30 to 08/07/2019 09:55
    how to calculate time

  21. How to write at a time if conditions and eomonth in excel. I am write a one condition =if(C1=“FALSE”,EOMONTH(D3),B40,””) but this is wrong plz tel me correct answer (C1=Cell address, D3=Cell address, B40=Cell address there another one date)

  22. Hi guys,
    Can you help me with something ?
    i want to compare 2 calendar dates in excel , for example:
    DATE 1 - DATE 2
    10/3/2017--2/6/2020
    1/17/2019--1/17/2020
    9/25/2017--1/25/2019
    3/21/2018--3/21/2019
    as some of the days have been done in the past I would need to compare the dates between themselves and between "today"'s date to have am update of the process any time ;as this dates represent completion days of our colleagues training.
    DATE 1 is the date when the training has been completed
    DATE 2 is the due date for the next training
    If the date is DATE 2 < TODAY by 3 moths range it will be highlighted with red
    if the date is DATE 2 TODAY it will be highlighted with amber/yellow
    if the date is DATE 2 > TODAY it will be highlighted in green
    can you guys help me :)

  23. Calculate number of date between dates and days in Excel

  24. Hi,
    I have confusion :- my start date is - March 01, 2019 and end date - today (March 27, 2019) if i set formula it is calculating 26 Days but if i count it manually it is calculation 27 Days. I don't understand what is my problem.... Please provide me a solution......

  25. I'm trying to track evaluations. I want the same month and day as their date of hire, but this year to be included. For example, date of hire: 2/14/1997, next eval: 2/14/2019. Is there a formula for this?

  26. hi
    i am not able to use this formula in windows10

    =DATEDIF(M89, N89, "y") &" years, "&DATEDIF(M89, N89, "ym") &" months, " &DATEDIF(M89, N89, "md") &" days" it showing #value!
    and same for this also
    =(MID(T88,4,2)&"/"&LEFT(T88,2)&"/"&RIGHT(T88,2))+0

    kindly help on it

  27. I have a date i.e 22-Mar-1968 and I want to add in this date only 23 year. what will be the formula in excel. please guide me.

    1. Please reply my question posted on 12 February 2019. With Thanx

  28. I need to calculate a % of a task completed within 5 days. In brief:
    * Column J = Notified Date
    * Column M = Completed Date
    * Column N = work days it took to complete task
    KPI is 5 Days, so I need to work out from Column N if this was within 5 days, ie. what the % rate was. If 5 days or less, then response would be 100%. If less than 5 days, then display the % rate.
    Is there a formula that can calculate this %. Thanks :)

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

    Please solve my problem as soon as your free time Thanks... :)

  30. not working with office 2007, please help

  31. Thanks, it was very helpful.

  32. I have to be able to calculate the total number of years, months, and days of experience for many employees. I have used the DATEDIF function to calculate the time elapsed between the dates for each individual job. However, I am trying to figure out a way of adding the resulting figures to come up with a comprehensive time calculation (ie. enter the start and end dates to come up with a calculation that the employee worked 1y, 2m, 28d at one job, and 3y, 5m, 7d at a second job, and then have a separate field that is able to tell me that the employee has a total of 4y, 8m, 5d of experience).

    1. Did you get an answer for this? I'm trying to do the same thing.

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

  34. I have no datedif function in Excel 2013, but instead have datediff function which is different. Is there a method that I can use datedif function in VBA Excel 2013?

  35. Luis:
    You need to enter the date in a format your version of Excel recognizes as a date like 1/1/18 is January 1st 2018 in my version.
    If your version supports DATEDIF, then you can enter this formula in the display cell of your choice
    =ROUNDDOWN(DATEDIF(A47,D47,"D")/7,0)
    So, if A47 holds the date 3-Apr-14 and D47 holds the date 1-Sep-18 this formula will return 230.
    Be sure to format the cells as dates.
    You can change the A47 and D47 to the cell addresses that suit you.

  36. Hi
    Maybe someone can help me, I need to determine the difference in weeks for example: the difference between week year - week year (10 2018 - 22 2018)= 12
    Thakns

  37. 21-09-1981 36 Years, 8 Month,17 Days
    Date is blank 118 Years, 5 Month,8 Days ( I want to show this colum empty)
    118 Years, 5 Month,8 Days
    118 Years, 5 Month,8 Days
    I issued this formula for date calculation DATEDIF($F5,TODAY(),"y")&" Years, " &DATEDIF($F5,TODAY(),"ym")&" Month,"&DATEDIF($F5,TODAY(),"md")&" Days"

    When date cell is empty formula cell show blank please help solve to this problem

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

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

  40. hi there, can someone help me, i need help in column in where it would say "ready" if its within a year from today's date and the other will say "under construction" if its still over a year from today's date.

    PROJECT TO DATE READY/UNDER CONSTRUCTION
    SA December 31, 2012 April 11, 2018
    SB June 30, 2013 April 11, 2018
    SC June 30, 2013 April 11, 2018
    SD June 30, 2013 April 11, 2018
    SE December 31, 2013 April 11, 2018
    SF March 31, 2014 April 11, 2018
    SG January 31, 2015 April 11, 2018
    SH February 28, 2015 April 11, 2018
    SI June 30, 2015 April 11, 2018
    SJ June 30, 2015 April 11, 2018
    SK October 31, 2015 April 11, 2018
    SL December 31, 2015 April 11, 2018
    SM March 31, 2016 April 11, 2018
    SN June 30, 2016 April 11, 2018
    SO November 30, 2016 April 11, 2018
    SP December 31, 2016 April 11, 2018
    SQ March 31, 2017 April 11, 2018
    SR June 30, 2017 April 11, 2018
    SS July 31, 2017 April 11, 2018
    ST August 31, 2017 April 11, 2018
    SU February 12, 2018 April 11, 2018
    SV February 28, 2018 April 11, 2018
    SW September 30, 2018 April 11, 2018
    SX October 31, 2018 April 11, 2018
    SY November 30, 2018 April 11, 2018
    SZ January 31, 2019 April 11, 2018
    RA May 31, 2019 April 11, 2018

  41. I want to equal 1 year to date

  42. Hi,
    This formula =NOW()-A1 does not work for same day calculation:

    if today is 18 Mar 2018, the result shows a month!

    For example A1 is 18 Mar 2018 07:10:00PM

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

  44. how to set age limit for ex: 25.3.1978 to 15.4.1983, if age is between 35 to 40, i have to set 35 how to do it

  45. Hi there. Newbie to Excel, so no expert by any stretch. I'm trying to work out how to do a formula to count days within a month only. I have two columns titled "Start Date" and "Finish Date". For example. Start Date is 22 Jan 18 and Finish Date is 11 Feb 18. Is there a formula to count the number of days (within those two columns) for the month of January 2018 only? It would equal 9 days. I then need another formula, same example as above, to calculate the days that would be returned for February 2018 only, ie. would equal 10 days. The total amount of lines within the speadsheet could be anywhere up to 500 lines. I have found formulas to count the total days, but not exactly for a month only. Hope that makes sense. Thanks.

  46. What about if you use the month of February. If you deduct 28 feb 2018 and 31 Jan 2018 it will be zero month

  47. Hi,

    How to calculate difference in two dates in days when the date is selected/inserted from calendar through Microsoft Date and Time Picker Control.
    This formula is not working =DATEDIF(A2, B2, "d") because it is not considering cell to the cell where date is selected from picker.

    Thanks

  48. Hi, I got a problem in calculating for year month. I would like to add one month for every month.

    Example:20Yr(s) 4 Mth(s)
    (I would like to auto add one month for the example. Please give me the formula).

    thanks.

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

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

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