Excel DATEDIF Function
DATEDIF Function: A Brief
The DATEDIF function in Excel calculates the difference between two dates in years, months, or days. The name DATEDIF stands for Date + Difference, meaning it calculates the difference between two dates. You must need to enter the unit for returning the values otherwise this function will return #NUM! error in the cell.
| Objective | Value Returned by function |
|---|---|
| Aim to get the difference between the two dates. | DATEDIF Function will return a valid numeric values in the form of days, months, years. |
DATEDIF Function: A Syntax
=DATEDIF(starting_date,ending_date,"unit") // Unit must be added compulsory.
- Starting_date: The cell must contain a valid starting date in the date format.
- Ending_date: The cell must contain a valid ending date in the date format.
- unit: The cell must contain the valid unit for the function to return the values.
| Unit | Explanation |
|---|---|
| y | This unit returns the difference in complete years between the two dates. |
| m | This unit returns the difference in complete months between the two dates. |
| d | This unit returns the difference in complete days between the two dates. |
| md | Returns the difference in days but the function ignores both month and years |
| ym | Returns the difference in months but the function ignores both days and years |
| yd | Returns the difference in days but the function ignores years |
Basic Example Usage of DATEDIF Function:
The formulas below explains how to use the DATEDIF function to calculate the total years, months, and days between April 1, 2021 (01-04-2021) and March 30, 2025 (30-03-2025).
The formulas remain the same, except for the unit used:
=DATEDIF(B4,C4,"y") // Formula returns the complete year as 3
=DATEDIF(B4,C4,"m") // Formula returns the complete months as 47
=DATEDIF(B4,C4,"d") // Formula returns the complete days as 1459
Simple usage of DATEDIF function
The explanations of results from the above formula are,
- There are 3 complete years between the given dates. Although the total difference is 47 months, DATEDIF ignores the extra 11 months since they do not form a complete year.
- The total difference is 47 months. DATEDIF always rounds down to the nearest full month.
- There are 1,459 days between the two dates.
Example 2: Finding the Differences in Days using DATEDIF Function:
The below example formula will explains you how to use the datedif function to find the followings:
- Difference in days
- Difference in days ignoring years
- Difference in days ignoring both month and years
Excel formula to find the difference between days using datedif function
=DATEDIF(B4,C4,"d") //Returns the value 1459 as days
=DATEDIF(B5,C5,"yd") //Returns the value 335, ignores years
=DATEDIF(B6,C6,"md") //Returns the value 30, ignores both years and months
Example 3: Finding the Differences in Months using DATEDIF Function:
For finding the difference in months, you can only use two units and they are “m, ym“
Example of finding difference in months using datedif function
The formulas used to explain the working of finding the differences in month in the above image are,
=DATEDIF(B4,C4,"m") //Returns the months as 47
=DATEDIF(B5,C5,"ym") //Returns the months as 11, ignores years
The 1st formula returns the value as 47 for the dates between 01-04-2021 and 30-03-2025, which is absolutely correct.
The 2nd formula returns the value as 11 for the dates between 01-04-2023 and 01-03-2025, which is also correct. The DATEDIF function roundoffs to the nearest complete year. In this case, it calculates the difference in months after accounting for full years and ignores the extra 11 months since they do not form a complete year.
Example 3: Finding the Differences in Years using DATEDIF Function:
For finding the difference in months, you can only use one unit and which is “y”.
Formula to returns the differences between two dates in a complete years
The formula used in the above image is,
=DATEDIF(B4,C4,"y") //Returns the value 5
=DATEDIF(B5,C5,"y") //Returns the value 4
The first formula returns 5 years for the dates between April 1, 2020 (01-04-2020) and August 30, 2025 (30-08-2025), which is correct. The total number of completed years between these two dates is 5.
For the second formula, the DATEDIF function returns 4 years because the ending date is February 1, 2025 (01-02-2025). Since two more months are needed to complete another full year, DATEDIF ignores the incomplete year and returns the last fully completed year count as 4.
Other Nested Functions to calculate the difference b/w two dates in the form of years:
You can other excel functions, to find the differences between the two dates in the form of years. Some of them are:
Using YEARFRAC Function:
=INT(YEARFRAC(B4, C4, 1)) // Returns the Differences in years
Using YEARFRAC function to find the differences between two dates in years
Using IF, DATE, MONTH, DAY Functions:
=YEAR(C4) - YEAR(B4) - IF(DATE(YEAR(C4), MONTH(B4), DAY(B4)) > C4, 1, 0)
Using Nested functions to find difference between two dates in the form of years
Using EOMONTH Function:
This function will ensures a correct year difference by considering end-of-month dates. It will adjusts the year count if the starting date hasn’t occurred yet.
=YEAR(C4) - YEAR(B4) - IF(EOMONTH(B4, 0) > C4, 1, 0) //Returns 5
=YEAR(C5) - YEAR(B5) - IF(EOMONTH(B5, 0) > C5, 1, 0) //Also Returns 5
Using EOMONTH to find the difference between two dates accurately
The EOMONTH Function will consider the end of the month between the starting date and the ending date. As a result, the 2nd formula returns 5 years, even though the difference between the two dates does not end with a fully completed year.
Using DATEDIF function to returns Years, Months and Days Value:
To do this, you may need to adjust the formula with words to display the number with preety form.
=DATEDIF(B4, C4, "Y") & " Years, " & DATEDIF(B4, C4, "YM") & " Months, " & DATEDIF(B4, C4, "YD") & " Days"
The above formula returns as 5 Years, 4 Months, 151 Days
Formula to display years, months, days using datedif functions
By adjust the DATEDIF formula, you can easily print the difference between the two dates with words as shown in the above image.
Using YEAR Function:
By using the YEAR function, you can calculate the difference in years between two dates. However, it does not return the exact number of completed years. Instead, it gives the full difference between the years of the two dates.
=YEAR(C4) - YEAR(B4)
Using YEAR function in the formula
As you can see in the above image, the formula returns the full difference between the given years.
Using DATEDIF Function to Calculate Age from Birthday:
You can use the DATEDIF function to calculate age from birthday. I wrote a detailed article explaining the multiple formula to calculate the age from date of birth.
=DATEDIF(C4, TODAY(), "Y") //Return the AGE from the birthday.
Using DATEDIF function to find AGE from birthday
You can use the above formula to calculate the accurate age from the given birthday using the DATEDIF function in the formula.
That’s it.
Conclusion:
I’ve explained the usage of DATEDIF function with clear cut practical examples. If you have any queries with the above topic and then comment us below. Find more interesting tutorials on our homepage: Excel24x7.com
Top comments (0)