How to use DATEDIF Function in Excel


Try to type “DATEDIF” in a cell in your worksheet, you won’t find anything listed with this name.

try to type datedif in a cell

Even if you check help system...

...there is nothing listed with this name.

try to search for datedif in help selection

But, if you step back to the help system of the Excel 2000, you will find DATEDIF function documented there.

Well, it’s a mystery that why Microsoft has hide the DATEDIF Function from functions list.

If you ask me, DATEDIF is the best function to get the difference between two dates.

And, one of its best examples is calculating a person’s age using his date of birth.

Now the question is:

If this function is not there, how we can use it in all the new versions of Excel?

And the answer is:

We can use it like we use all the Excel functions. The only difference is when you try to insert it Excel will not show it’s syntax arguments.

So today, in this post, I’d like to share with you all the details about DATEDIF function.

Let’s get started.

Syntax

DATEDIF(Date1,Date2,Interval)

Date1: The date from where you want to start the calculation of your interval. In simple words, it is the start date of the interval.

Date2: The date to which you want to calculate the interval. In simple words, it is the end date of your interval.

Interval: It represents the unit of the time, which will return from the function. We have six specific text strings to represent different time units.

Using Different Intervals

You have 6 different types of intervals to calculate the difference between two dates.

“y” for Years

It will return the complete years between the start date and the end date ignoring the months and days which are in excess.

using y interval in datedif to get years

In the above example, you have calculated the interval between two dates by using “y”.

If you actually check, the difference between these two dates is more than 25 years (37 days more).

But, it has only returned the completed years, that is 25.

“m” for Months

It will return the number of complete months between the start date and end date by ignoring the excess days.

using m interval in datedif to get months

It has returned 301 months in the result which are the total completed months ignoring excess 7 days.

“d” for Days

It will return the total days between the start date and end date. As days are the smallest units between two dates, that’s why you will get total days here.

using d interval in datedif to get days

In the above example, we have total days between the start and end date.

“ym” for Months over Years

It will return numbers of the completed months over the completed years.

In simple words, if you have the total interval of 25 years 1 month and 7 days between two dates, it will return 1 month in the result.

using ym interval in datedif to get months over years

In the above example, we have calculated the number of complete months left after competing years between the start date and end date.

“yd” for Days over Years

It will return the number of the days over the complete years.

In the simple words, if you have a total interval of 25 years and 37 days between two dates, it will return 37 days in the result.

using yd interval in datedif to get days over years

In the above example, you have calculated the number of days left after the complete years between the start date and end date.

“md” for Days over Months

It will return the excess number of days over the complete months.

In simple words, if you have the total interval of 301 months and 7 days between two dates, it will return 7 days in the result.

using md interval in datedif to get days over months

In the above example, we have calculated the number of days left after the complete months between the start date and end date.

Things To Remember

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

7 thoughts

Leave a Comment

Your email address will not be published.

  1. Hi Puneet, is it possible to use this in Dax? Or indeed PowerQuery? Especially where one is required to sum up a list of start dates – end dates to get a subtotalled yy mm dd. I’ve been able to do this in excel but struggling with the other two…

    Reply
  2. Hi Puneet,

    Would you be able to see working days only? i.e.: If a job card is 7 days old I would like to see how many work days (Monday – Friday) the device is in for repairs and not the entire period as we only count working days. Hope there is a way?

    Reply
  3. You have ‘Using “ym” as Interval’ as a heading twice. I believe the second one should be ‘Using “yd” as Interval’ as that is what you’re describing below it in the paragraph. 🙂

    Reply
    • Nice one Puneet, Puneet I have one query how to count how many years one completed, which is condition and for year it is 240 days ie condition is “>=240″ days.First we have to calculate sum of days one completed in 3 years.It is data for making seniority list.There are three columns specifying days in 3 consecutive years.Puneet plz help me how to calculate it in excel. My colleague apply formula countif(range,”>=240″) but in some part of data it is giving wrong result. Plz Puneet help if you can and I know you can.If you have any function to calculate seniority list in excel in 2007 plz reply

      Reply