Try to type “DATEDIF” in a cell in your worksheet, you won’t find anything listed with this name.
Even if you check the help system there is nothing listed with this name.
But, if you step back to the help system of Excel 2000, you will find the DATEDIF function documented there. Well, it’s a mystery why Microsoft must hide the DATEDIF Function from the 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 calculated in age.
Now the question is: If this function is not there, how can we 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 its syntax arguments. So today, in this post, I’d like to share with you all the details about the DATEDIF function.
- 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 union 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 distinct types of intervals to calculate the difference between two dates.
1. “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.
In the above example, you have calculated the interval between two dates by using “y”. If you 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.
2. “m” for Months
It will return the number of complete months between the start date and end date by ignoring the excess days.
It has returned 301 months in the result which are the total completed months ignoring the excess of 7 days.
3. “d” for Days
It will return the total days between the start date and the end date. As days are the smallest units between two dates, that’s why you will get total days here.
In the above example, we have the total days between the start and end dates.
4. “ym” for Months over the Years
It will return the numbers of the completed months over the completed years. In simple words, if you have a total interval of 25 years 1 month, and 7 days between two dates, it will return 1 month in the result.
In the above example, we have calculated the number of complete months left after competing years between the start date and end date.
5. “yd” for Days over Years
It will return the number of days over the completed years. In 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.
In the above example, you have calculated the number of days left after the complete years between the start date and end date.
6. “md” for Days over Months
It will return the excess number of days over the complete month. In simple words, if you have a total interval of 301 months and 7 days between two dates, it will return 7 days in the result.
In the above example, we have calculated the number of days left after the complete months between the start date and end date.
EXAMPLE: Calculate Age in Excel using DATEDIF
Here you need to calculate age in a format in which you could show years, months, and days separately. For example, 26 Years, 8 months & 17 days. And for this, we need to combine DATEDIF three times.
Here are the steps.
1. Calculate the Completed Years of a Person’s Age
First of all, you need to calculate to total completed years between a date of birth and today’s date. And, for this the formula will be:
This will return completed years between two dates.
2. Calculate Completed Months of a Person’s Age after Completed Years
Next thing is to calculate the completed months. It means if your age is 26 Years, 5 months, and 19 days, we need to get 5 months in return.
The below formula can be used:
This will return the completed months between the date of birth and today’s date after the completed years.
3. Completed Days of a Person’s Age after Complete Years and Months
In the end, you need to calculate the days that are left after completing years and months. Here is the formula:
This will return the rest of the days between the date of birth and today’s date after completing years and months.
4. Combine All Three Formulas to Get the Actual Age
Now it’s time to combine all these to create an accurate age. Enter the formula below in a cell in your worksheet and replace Date-of-Birth with your actual birth date.
="You age is "& DATEDIF(Date-of-Birth,TODAY(),"y") &" Year(s), "& DATEDIF(Date-of-Birth,TODAY(),"ym")& " Month(s) & "& DATEDIF(Date-of-Birth,TODAY(),"md")& " Day(s)."
- It will ignore the time in date.
- The end date (Date2) should be greater than the start date (Date1).
- Compare Two Dates in Excel
- Convert Date to Number in Excel
- Convert Text to Date in Excel
- Create a Date Range in Excel
- Custom Date Formats in Excel
- Highlight Dates Between Two Dates in Excel
- Quickly Concatenate Two Dates in Excel
- Random Date Generator (Excel Formula)
- Combine Date and Time in Excel
⇠ Back to Excel Formulas List (Basic + Advanced)
9 thoughts on “Excel’s DATEDIF Function (Quick Guide with an Example)”
I would like to display a School Year date range i.e. 22-23, 23-24,24-25 etc…
Does anyone know the syntax to do this from a formula?
I need this formula to not future date. For example if i put in 08/2021 i need the result to be 08/2021 or if i put in 07/2021 to return it as 07/2021. I need for it to continue to calculate the last quarter for months before 07/20201.
Now I am learning Hidden Functions as well 🙂
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…
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?
Really valuable tip. I’ve always done it using Int and Frac
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. 🙂
Just Corrected Now.
You are Awesome. 🙂
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