May
28

Excel DATEDIF - calculate difference between two dates 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.

Over the past few weeks, we investigated nearly every aspect of working with dates and times in Excel. If you have been following our blog series, you already know how to insert and format dates in your worksheets, how to calculate days, weeks, months and years as well as add and subtract dates.

In this tutorial, we will focus on calculating date difference in Excel and you will learn different ways to count the number of days, weeks, months and years between two dates.

Excel DATEDIF function - calculating date difference

As its name suggests, the DATEDIF function is purposed for calculating the difference between two dates.

DATEDIF is one of very few undocumented functions in Excel, and because it is "hidden" you won't find it on the Formula tab, nor will you get any hint on which arguments to enter when you start typing the function's name in the formula bar. That is why it's important to know the complete syntax of Excel DATEDIF to be able to use it in your formulas.

Excel DATEDIF function - syntax

The syntax of the Excel DATEDIF function is as follows:

DATEDIF(start_date, end_date, unit)

All three arguments are required:

Start_date - the initial date of the period you want to calculate.

End_date - the ending date of the period.

To put it differently, start_date and end_date are two dates to calculate the difference between. In your DATEDIF formulas, you can input the dates is various ways, such as:

  • Cell references. For example, the following formula counts the number of days between the dates in cells A1 and B1:

    =DATEDIF(A1, B1, "d")

  • Text strings. Excel understands dates in many formats, for example "20-May-2015", "5/20/2015", "2015/5/20", etc. The following formula calculates the number of months between the specified dates:

    =DATEDIF("5/10/2015", "7/20/2015", "m")

  • Serial numbers. Since Microsoft Excel stores each date as a serial number beginning with January 1, 1900, you can put numbers corresponding to the dates directly in the formula. Although fully supported, this method is not reliable because date numbering varies on different computer systems. In the 1900 date system, the following formula is another way to calculate months between two dates from the previous example (10-May-2015 and 20-Jul-2015):

    =DATEDIF(42134, 42205, "m")

  • Results of other functions. For instance, the following formula counts how many days there are between today's date and 20 May, 2015.

    =DATEDIF(TODAY(), "5/20/2015", "d")

Note. In your formulas, the end date must always be greater than the start date, otherwise the Excel DATEDIF function returns the #NUM! error.

Unit - the time unit to use when calculating the difference between two dates. By supplying different units, you can get the DATEDIF function to return the date difference in days, months or years. Overall, 6 units are available, which are described in the following table.

Unit Meaning Explanation
Y Years Number of complete years between the start and end dates.
M Months Number of complete months between the dates.
D Days Number of days between the start date and end date.
MD Days excluding years and months The date difference in days, ignoring months and years.
YD Days excluding years The date difference in days, ignoring years.
YM Months excluding days and years The date difference in months, ignoring days and years.

Hopefully, the above information has been helpful to understand the basics. And now, let's see how you can use the Excel DATEDIF function to compare dates in your worksheets and return the difference.

How to get the number of days between two dates in Excel

If you observed DATEDIF's arguments carefully, you've noticed that there exist 3 different units for counting days between the dates. Which one to use depends on exactly what your needs are.

Example 1. Excel DATEDIF formula to calculate date difference in days

Supposing you have the start date in cell A2 and the end date in cell B2 and you want Excel to return the date difference in days. A simple DATEDIF formula works just fine:

=DATEDIF(A2, B2, "d")

Provided that a value in the start_date argument is less than in end_date. In case the start date is greater than the end date, the Excel DATEDIF function returns the #NUM error, as in row 5:

Counting the number of days between two dates

If you are looking for a formula that can return the date difference in days as either a positive or negative number, simply subtract one date directly from the other:

=B2-A2

Please see How to subtract dates in Excel for full details and more formula examples.

Example 2. Count days in Excel ignoring years

Supposing you have two lists of dates that belong to different years and you wish to calculate the number of days between the dates as if they were of the same year. To do this, use a DATEDIF formula with "YD" unit:

=DATEDIF(A2, B2, "yd")
Excel DATEDIF formula to count days between two dates ignoring years

If you want the Excel DATEDIF function to ignore not only years but also moths, then use the "md" unit. In this case, your formula will calculate days between two dates as if they were of the same month and the same year:

=DATEDIF(A2, B2, "md")

The screenshot below demonstrates the results, and comparing it with the screenshot above can help understand the difference better.
DATEDIF formula to calculate days between two dates ignoring years and months

Tip. To get the number of working days between two dates, use the NETWORKDAYS or NETWORKDAYS.INTL function.

How to calculate date difference in weeks

As you probably noticed, the Excel DATEDIF function does not have a special unit to calculate date difference in weeks. However, there is an easy workaround.

To find out how many weeks there are between two dates, you can use the DATEDIF function with "D" unit to return the difference in days, and then divide the result by 7.

Calculating date difference in weeks in Excel

To get the number of full weeks between the dates, wrap your DATEDIF formula in the ROUNDDOWN function, which always rounds the number towards zero:

=ROUNDDOWN((DATEDIF(A2, B2, "d") / 7), 0)

Where A2 is the start date and B2 is the end date of the period you are calculating.
Calculating how many full weeks there are between two dates

How to calculate the number of months between two dates in Excel

Similarly to counting days, the Excel DATEDIF function can compute the number of months between two dates that you specify. Depending on the unit you supply, the formula will produce different results.

Example 1. Calculate complete months between two dates (DATEDIF)

To count the number of whole months between the dates, you use the DATEDIF function with "M" unit. For example, the following formula compares the dates in A2 (start date) and B2 (end date) and returns the difference in months:

=DATEDIF(A2, B2, "m")

Note. For the DATEDIF formula to calculate months correctly, the end date should always be greater than the start date; otherwise the formula returns the #NUM error.

DATEDIF formula to calculate months between two dates

To avoid such errors, you may force Excel to always perceive an older date as the start date, and a more recent date as the end date. To do this, add a simple logical test:

=IF(B2>A2, DATEDIF(A2,B2,"m"), DATEDIF(B2,A2,"m"))

Example 2. Get the number of months between two dates ignoring years (DATEDIF)

To count the number of months between the dates as if they were of the same year, type "YM" in the unit argument:

=DATEDIF(A2, B2, "ym")
Calculating months between two dates ignoring years

As you see, this formula also returns an error in row 6 where end date is less than the start date. If your data set may contain such dates, you will find the solution in the next examples.

Example 3. Calculating months between two dates (MONTH function)

An alternative way to calculate the number of months between two dates in Excel is using the MONTH function, or more precisely a combination of MONTH and YEAR functions:

=(YEAR(B2) - YEAR(A2))*12 + MONTH(B2) - MONTH(A2)

Of course, this formula is not so transparent as DATEDIF and it does take time to wrap your head around the logic. But unlike the DATEDIF function, it can compare any two dates and return the difference in months as either a positive or negative value:
Calculating months between two dates using the MONTH and YEAR functions

Notice that the YEAR/MONTH formula has no problem with calculating months in row 6 where the start date is more recent than the end date, the scenario in which an analogues DATEDIF formula fails.

Note. The results returned by DATEDIF and YEAR/MONTH formulas are not always identical because they operate based on different principles. The Excel DATEDIF function returns the number of complete calendar months between the dates, while the YEAR/MONTH formula operates on months' numbers.

For example, in row 7 in the screenshot above, the DATEDIF formula returns 0 because a complete calendar month between the dates has not elapsed yet, while YEAR/MONTH returns 1 because the dates belong to different months.

Example 4. Counting months between 2 dates ignoring years (MONTH function)

In case all of your dates are of the same year, or you want to calculate months between the dates ignoring years, you can the MONTH function to retrieve the month from each date, and then subtract one month from the other:

=MONTH(B2) - MONTH(A2)

This formula works similarly to Excel DATEDIF with "YM" unit as demonstrated in the following screenshot:
Counting months between 2 dates ignoring years with MONTH function

However, the results returned by two formulas differ is a couple of rows:

  • Row 4: the end date is less than the start date and therefore DATEDIF returns an error while MONTH-MONTH yields a negative value.
  • Row 6: the dates are of different months, but the actual date difference is just one day. DATEDIF returns 0 because it calculates whole months between 2 dates. MONTH-MONTH returns 1 because it subtracts the months' numbers from each other ignoring days and years.

How to calculate years between two dates in Excel

If you followed the previous examples where we calculated months and days between two dates, then you can easily derive a formula to calculate years in Excel. The following examples can help you check if you got the formula right :)

Example 1. Calculating complete years between two dates (DATEDIF function)

To find out the number of complete calendar years between two dates, use the old good DATEDIF with the "Y" unit:

=DATEDIF(A2,B2,"y")

DATEDIF function to calculate complete years between two dates

Notice that the DATEDIF formula returns 0 in row 6, although the dates are of different years. This is because the number of full calendar years between the start and end dates equals to zero. And I believe you are not surprised to see the #NUM! error in row 7 where the start date is more recent than the end date.

Example 2. Calculating years between two dates (YEAR function)

An alternative way to calculate years in Excel is using the YEAR function. Similarly to the MONTH formula, you extract the year from each date, and then subtract the years from each other:

=YEAR(B2) - YEAR(A2)

In the following screenshot, you can compare the results returned by the DATEDIF and YEAR functions:
Calculating years between two dates using the YEAR function

In most cases the results are identical, except that:

  • The DATEDIF function calculates complete calendar years, while the YEAR formula simply subtracts one year from the other. Row 6 illustrates the difference.
  • The DATEDIF formula returns an error if the start date is greater than the end date, while the YEAR function returns a negative value, as in row 7.

How to get date difference in days, months and years

To count the number of complete years, months and days between two dates in a single formula, you simply concatenate three DATEDIF functions:

=DATEDIF(A2, B2, "y") &" years, "&DATEDIF(A2, B2, "ym") &" months, " &DATEDIF(A2, B2, "md") &" days"
Calculating date difference is days, months and years

If you'd rather not display zero values, you can wrap each DATEDIF in the IF function as follows:

=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"

The formula displays only non-zero elements as demonstrated in the following screenshot:
Calculate date difference is days, months and years ignoring zero values

DATEDIF formulas to calculate age in Excel

In fact, calculating someone's age based on the date of birth is a special case of calculating date difference in Excel, where the end date is today's date. So, you use a usual DATEDIF formula with "Y" unit that returns the number of years between the dates, and enter the TODAY() function in the end_date argument:

=DATEDIF(A2, TODAY(), "y")

Where A2 is the birth date.

The above formula calculates the number of complete years. If you'd rather get the exact age, including years, months and days, then concatenate three DATEDIF functions like we did in the previous example:

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

And you will get the following result:
DATEDIF formula to calculate age in Excel

To learn other methods of converting a birthdate to age, check out How to calculate age from date of birth.

Date & Time Wizard - easy way to build date difference formulas in Excel

As demonstrated in the first part of this tutorial, Excel DATEDIF is quite a versatile function suitable for a variety of different uses. However, there is one significant drawback - it is undocumented by Microsoft, meaning, you won't find DATEDIF in the list of functions nor will you see any argument tooltips when you start typing a formula in a cell. To be able to use the DATEDIF function in your worksheets, you have to remember its syntax and enter all the arguments manually, which might be a time-consuming and error-prone way, especially for beginners.

Ultimate Suite for Excel changes this radically as it now provides the Date & Time Wizard that can make almost any date difference formula in no time. Here's how:

  1. Select the cell where you want to insert the formula.
  2. Go to the Ablebits Tools tab > Date & Time group, and click the Date & Time Wizard button:
    Date & Time Wizard for Excel
  3. The Date & Time Wizard dialog window shows up, you switch to the Difference tab and supply data for the formula arguments:
    • Click in the Date 1 box (or click the Collapse Dialog button to the right of the box) and select a cell containing the first date.
    • Click in the Date 2 box and select a cell with the second date.
    • Select the desired unit or combination of units from the Difference in drop-down menu. As you do this, the wizard lets you preview the result in the box and the formula in the cell.
    • If you are happy with the preview, click the Insert formula button, otherwise try different units.

For example, this is how you can get the number of days between two dates in Excel:
Getting the number of days between two dates

Once the formula is inserted in the selected cell, you can copy it to other cells as usual by double-clicking or dragging the fill handle. The result will look similar to this:
The formula to calculate the difference between two dates in days

As another example, let's get the difference between two dates in years, months, weeks and days. For this, you select the Y + M + W + D combination and get the following results:
The formula to calculate date difference in years, months, weeks and days.

If you'd rather not show years or months, select Exclude years and/or Exclude months box or choose another combination of units from the drop-down list.

Depending on the layout and design of your worksheet, you can present the results in one of the following ways:

  • Add full text labels like days, months, weeks, years. It is the default option demonstrated in the above examples.
  • Add short text labels like d., w., m., y. For this, you select the Add text label box and Short labels radio button.
  • Show no text labels. To remove text labels from the results, clear the Add text label box. In this case, a single unit will be output as a number, multiples units will be separated with forward slashes like shown in the screenshot below:

Difference between two dates in years, months and days

Benefits of using Date & Time Formula Wizard

Apart from speed and simplicity, the Date & Time Wizard provides a few more advantages:

  1. Unlike a regular DATEDIF formula, an advanced formula created by the wizard does not care which of the two dates is smaller and which is larger. The difference is always calculated perfectly even if Date 1 (start date) is greater than Date 2 (end date).
  2. The wizard supports all possible units (days, weeks, months and years) and lets you choose from 11 different combinations of these units.
  3. The formulas the wizard builds for you are normal Excel formulas, so you are free to edit, copy or move them as usual. You can also share your worksheets with other people, and all formulas will remain in place, even if someone does not have the Ultimate Suite in their Excel.

If you are curious to try the Date & Time Wizard in your worksheets, you are welcome to download 14-day trial version. And if you like the tool and decide to get a license of the Ultimate Suite, be sure to grab the 15% off coupon code that we provide exclusively to our blog readers: AB14-BlogSpo.

This is how you compute the difference between two dates in various time intervals. Hopefully, the DATEDIF function and other formulas you've learned today will prove useful in your work. Anyway, I thank you for reading and invite to check out other tutorials to working with days and time in Excel.

You may also be interested in:

166 Responses to "Excel DATEDIF - calculate difference between two dates in days, weeks, months or years"

  1. Grace says:

    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?

  2. Aries says:

    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.

  3. Moxie says:

    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.

  4. Jenny Thong says:

    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?

  5. Hanni says:

    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.

  6. Rab Nawaz says:

    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: ______ _______ _____

  7. Gib says:

    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.

  8. Andy says:

    Dear,

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

  9. Ashraf says:

    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?

  10. Shivansh says:

    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?

  11. imtiaz ahmed says:

    I need between date different by this below format

    Start Date 19.02.1989
    End Date 01.02.2017

  12. Neha says:

    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.

  13. Lisa M says:

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

  14. Mahesh K says:

    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.

  15. Samee says:

    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

  16. Wint Wah Wah Htun says:

    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

  17. Lauren says:

    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

  18. alex says:

    dateif doesn't exists

  19. Ashok Sahu says:

    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.

  20. Paras says:

    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

  21. Doug Marcus says:

    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.

  22. Nitin says:

    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

  23. Dan says:

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

  24. Wayne says:

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

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

  25. Kim says:

    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?

    • Kim says:

      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?

  26. Ahmer says:

    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

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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard