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 3. Total comments: 292

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

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

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

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

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

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

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

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

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

  10. not working with office 2007, please help

  11. Thanks, it was very helpful.

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

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

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

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

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

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

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

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

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

  21. I want to equal 1 year to date

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

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

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

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

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

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

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

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

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

  31. I find the date functions flawed as they take an extra day away from the result.

    01-NOV-2017 to 30-NOV-2017 should be 30 days, but every calculation I use shows 29. Why are the date functions missing a day?

  32. I want to do the same but count the number of months going forward from say Aug to May in that format... Any thoughts?

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =DATEDIF(DATE(2016,8,31),DATE(2017,5,1),"M")

      Hope this will help you!

  33. Hi! Thanks for these instructions - the site/tutorials are great and I've learned a lot! I was wondering if you had any tips for me:
    I am trying to display the date, that is x number of days after Jan 1.

    I used:
    A1 =datedif("01/01/2017","11/27/2017", "d")
    to display how many days it has been since the start of the year.
    I am now trying to display the date 30 days prior.
    I was able to find the day number this year by:
    =datedif("1/1/17", A1, "d")- 30
    For instance, this equals 300. How do I display the 300th day of the year (i.e. October 27)?
    Thanks!

  34. How to calculate time in excel such as
    31/10/17 10:45:00

    01/11/17 02:15:21

    Please advise us above query

    1. Hello, Ahmer,

      Please try the following formula:

      =DAYS("01/11/17 02:15:21"-"31/10/17 10:45:00",0)*24 + HOUR("01/11/17 02:15:21"-"31/10/17 10:45:00") & " hours " & MINUTE("01/11/17 02:15:21"-"31/10/17 10:45:00") & " minutes " & SECOND("01/11/17 02:15:21"-"31/10/17 10:45:00") & " seconds"

      Hope it will help you.

  35. I am using the datedif function to calculate years of service for employment. I have that part down with the exception of employees that had a break in service. How do I add their previous years served before their break in service to their current years of service?

    1. Example:
      Current Date of Hire: 9/25/1999 to Present
      Current Time in Service: 18Y, 1M
      Previous Date of Hire: 5/23/1987
      Previous Termination Date:9/20/1991
      Previous Time in Service: 4Y, 3M

      How to I create a formula to add the current time in service along with the previous time in service?

  36. The datedif function no longer exists inf Office 2016. As usual, when the imbeciles at Microsoft "improve" a product, they remove useful functions.

    1. Hi Wayne,

      DATEDIF still works in Excel 2016. But it is undocumented, meaning you won't find it in the list of functions. To use DATEDIF in your formulas, you need to remember its syntax and type all the arguments manually.

  37. I'm trying to do this in excel but it doesn't give me the option for DATEDIF, only DATE or DATEVALUE...so I'm not sure what to do. Do you guys have a different formula that I can use without that function?

    Weird because I have the newest version...

  38. can any one help me for Excel formula with using IF STATEMENT for calculate tax 5% on OR BEFORE dt 14/9/2017 & 6% if dt 15 or after 15/9/2017
    A B C D
    1 DATE AMOUNT 5% TAX 6% TAX
    2 5/9/2017 2500 ?Formula ?Formula If date A column less than
    3 14/9/2017 1000 15/9/2017 tax 5% oterwise 6%
    4 15/9/2017 1000
    5 26/5/2017 1000
    6 15/10/2017 7000

  39. The most relevant unit of time measure for me is months between start and ending dates. Excel has a function, DATEDIF, which on surface sounds simple:

    =DATEDIF(‘Start Date’,’End Date’,M) will report the number of complete months between dates.
    That works 95% of the time but sometimes contracts cross leap years or they don’ start on the 1st day of the month or end on the last day of month.

    Here is my solution
    =ROUND(IF(TEXT(YEAR('Start Date'),0)&TEXT(MONTH('Start Date'),0)=TEXT(YEAR('End Date'),0)&TEXT(MONTH('End Date'),0),-1,DATEDIF(EOMONTH('Start Date',0)+1,EOMONTH('End Date',-1)+1,"M"))+(DATEDIF('Start Date',EOMONTH('Start Date',0),"D")+1)/DAY(EOMONTH('Start Date',0))+(DATEDIF(EOMONTH('End Date',-1),'End Date',"D"))/DAY(EOMONTH('End Date',0)),2)

    I know that it looks wordy but I store it in a most used functions worksheet so I can copy it into my worksheet.

    Below is sample of what it answers

    Start Date End Date Real Months
    1/1/2017 12/31/2017 12.00
    1/1/2017 1/2/2017 0.06
    1/5/2017 12/31/2021 59.87
    1/1/2017 2/28/2020 37.97
    1/1/2017 2/29/2020 38.00
    3/15/2017 3/14/2018 12.00
    1/5/2017 11/5/2017 10.04
    3/15/2017 11/5/2017 7.72
    3/1/2018 2/28/2019 12.00
    3/1/2018 2/29/2020 24.00
    3/1/2018 2/28/2020 23.97

    I have not found a bug is my most recent version of this function.

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

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

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

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

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

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

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

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

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

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

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

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