Determine the difference between two dates.
The Excel DATEDIF is an unknown compatibility function that can be used to calculate the difference between two dates. Example, to find a number of years between two dates, you may use =DATEDIF(start_date, end_date,”y”).
This function does not show in the Insert Function dialog box or Formula AutoComplete list. Therefore, enter this formula manually.
start_date – The start date
end_date – The day until the difference is to calculate
unit – The time unit option differ as per the required result. The DATEDIF include six different option for unit argument.
Integer representing the difference
Calculate the absolute difference between two dates based on the selected option in the unit parameter. The output will be in term of a number of days, months, or years as a difference between two dates. To find difference of days we can use separate function as well.
Note: The start_date argument must be before the end_date argument. Otherwise, the function returns an error.
The DATEDIF (Date Difference) function originates from Lotus 1-2-3, and for compatibility purpose, Excel includes as part of function although as a secret one.
The DATEDIF is a handy function to calculate the difference between start_date and end_date, but the result of this function depends on one of the six options for the third argument.
The following table provides valid codes for the third argument:
|“d”||Days||The number of days between start_date and end_date|
|“m”||Months||The number of complete months between start_date and end_date|
|“y”||Years||The number of complete Years between start_date and end_date|
|“ym”||Months excluding days and years||Return number of months between start_date and end_date, |
ignoring days and years between two dates.
|“yd”||Days excluding years||Return number of days between start_date and end_date, |
ignoring years between two dates.
|“md”||Days excluding months and years||Return number of days between start_date and end_date, |
ignoring the months and years between two dates.
Let’s check some examples to understand the application of Excel DATEDIF Function in the light of third arguments. Third argument based on Unit info. (above table) returns different type of value:
- To find difference of years between two dates we can simply DATEDIF function =DATEDIF(C6,D6,”y”), where cell reference is year.
Similarly we can calculate Months and Days between two dates.
- Using DATEDIF Excel function we can calculate “Days difference as if they were in the same month & same year”, using ‘md‘ as unit parameter.
- We can use DAYS function as well to returns difference between two dates.