Excel DATEDIF Function to calculate date difference

This tutorial explains how to use Excel DATEDIF Function to calculate date difference (difference between two dates) in complete year, month or day

What does Excel DATEDIF Function do?

DATEDIF function stands for “Date Difference“. It calculates the difference between two dates in complete year, month or day. Note that DATEDIF Function returns the “complete” value, for example, if the year difference between two dates is 1.99, the returned value is 1, not 2.

Syntax of Excel DATEDIF Function – calculate date difference

DATEDIF(start_date, end_date, format)

start_date	Begin date, can be in text format (within double quote) or date serial (number)
end_date	End date, can be in text format or date serial
format	A text that specifies day/month/year difference (see below) Format Returns “Y” The number of complete years in the period. “M” The number of complete months in the period. “D” The number of days in the period. “MD” The difference between the days in start_date and end_date. The months and years of the dates are ignored. “YM” The difference between the months in start_date and end_date. The days and years of the dates are ignored “YD” The difference between the days of start_date and end_date. The years of the dates are ignored.

Example of Excel DATEDIF Function – calculate date difference

The dates in below examples are formatted in d/m/yyyy

Formula	Result	Explanation
=DATEDIF(“1/9/2014″,”1/9/2014″,”D”)	0	1-1=0
=DATEDIF(“1/9/2013″,”31/8/2014″,”Y”)	0	Count 1 year if 31/8/2014 is passed. Calculation is based on complete year,no rounding or decimal places is returned
=DATEDIF(“1/9/2013″,”1/9/2014″,”Y”)	1	2014-2013=1
=DATEDIF(“1/7/2013″,”1/9/2014″,”YM”)	2	9-7=2
=DATEDIF(DATE(2013,7,1),DATE(2014,9,1),”YM”)	2	Express start_date and end_date in date serial

Limitation of Excel DATEDIF Function – calculate date difference

DATEDIF Function returns an integer, it fails to convert the date difference to month with decimal places or year with decimal places.

In my another article, I will write a custom Function to solve this issue.

Alternative of Excel DATEDIF Function – calculate date difference

You can use Year Function, Month Function, and Day Function to extract the necessary information to calculate the difference

Formula	Result	Explanation
=YEAR(A2)-YEAR(A1)	2	Year difference
=MONTH(A2)-MONTH(A1)	5	Month difference
=DAY(A2)-DAY(A1)	10	Day difference

Outbound References

https://support.office.com/en-za/article/DATEDIF-function-bd549d1c-f829-4691-a77d-4a1e3d42bc1a

Excel DATEDIF Function to calculate date difference