DATEDIF function is used to find the difference in days, months, or years between two dates. 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. years, months, or days Date Difference #NUM! 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. 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. Objective

Value Returned by function Objective Objective Value Returned by function 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. Aim to get the difference between the two dates. Aim to get the difference between the two dates. difference between the two dates. DATEDIF Function will return a valid numeric values in the form of days, months, years. DATEDIF Function will return a valid numeric values in the form of days, months, years. valid numeric values DATEDIF Function: A Syntax =DATEDIF(starting_date,ending_date,"unit") // Unit must be added compulsory. =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. Starting_date: The cell must contain a valid starting date in the date format. Starting_date: Ending_date: The cell must contain a valid ending date in the date format. Ending_date: unit: The cell must contain the valid unit for the function to return the values. unit: 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 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 Unit

Explanation Unit Unit Explanation Explanation y

This unit returns the difference in complete years between the two dates. y y This unit returns the difference in complete years between the two dates. 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. m m This unit returns the difference in complete months between the two dates. 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. d d This unit returns the difference in complete days between the two dates. 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 md md Returns the difference in days but the function ignores both month and years 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 ym ym Returns the difference in months but the function ignores both days and years Returns the difference in months but the function ignores both days and years yd

Returns the difference in days but the function ignores years yd yd Returns the difference in days but the function ignores years 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). years, months, and days April 1, 2021 March 30, 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 =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 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. 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. 3 complete years 47 months The total difference is 47 months. DATEDIF always rounds down to the nearest full month. 47 months There are 1,459 days between the two dates. 1,459 days 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 Difference in days Difference in days ignoring years Difference in days ignoring both month and years =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 =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“ m ym 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 =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. 47 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. 11 nearest complete year ignores 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”. The formula used in the above image is, =DATEDIF(B4,C4,"y") //Returns the value 5 
=DATEDIF(B5,C5,"y") //Returns the value 4 =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. first formula 5 years April 1, 2020 (01-04-2020) August 30, 2025 (30-08-2025) completed years 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. second formula DATEDIF 4 years February 1, 2025 (01-02-2025) two more months DATEDIF incomplete year fully completed 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 =INT(YEARFRAC(B4, C4, 1)) // Returns the Differences in years Using IF, DATE, MONTH, DAY Functions: =YEAR(C4) - YEAR(B4) - IF(DATE(YEAR(C4), MONTH(B4), DAY(B4)) > C4, 1, 0) =YEAR(C4) - YEAR(B4) - IF(DATE(YEAR(C4), MONTH(B4), DAY(B4)) > C4, 1, 0) 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 =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 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. 5 years 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 pretty 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 =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 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. full difference between the years =YEAR(C4) - YEAR(B4) =YEAR(C4) - YEAR(B4) 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. formula to calculate the age from date of birth =DATEDIF(C4, TODAY(), "Y") //Return the AGE from the birthday. =DATEDIF(C4, TODAY(), "Y") //Return the AGE from the birthday. You can use the above formula to calculate the accurate age from the given birthday using the DATEDIF function in the formula. DATEDIF function That’s it.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Arweave

Using Excel's TIME Function to Return Time From Hour, Minute, Seconds!

Using DATEDIF function to Return Difference Between any Two Dates! -> Excel24x7

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Using the Excel DAYS360 Function for Financial Analysis- A Guide

How to Calculate Age from Date of Birth in Excel? -> Excel24x7!

Using the Excel DAYS360 Function for Financial Analysis- A Guide

How to Calculate Age from Date of Birth in Excel? -> Excel24x7!

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps