This tutorial explains how to use Excel DATEDIF Function to calculate date difference (difference between two dates) in complete year, month or day
Excel DATEDIF Function
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.
If you want to calculate the Date Difference in month / year in decimal places, use Days360 function, which has an assumption that each month has 30 days.
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)
Example of Excel DATEDIF Function – calculate date difference
The dates in below examples are formatted in d/m/yyyy
|=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(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