To have a value which is the difference between two dates either in days, months or year, the Excel DATEDIF function can be used to carry this out. The Excel DATEDIF (Date +Difference) function is a “compatibility” function designed by Lotus 1-2-3. For some unknown reasons, the Excel DATEDIF function is only registered in Excel 2000, but it can be used in formulas in all the versions of Excel since 2000.
Use and Purpose of the Excel DATEDIF Function
Used in getting the difference in days, months, or years between two dates
The result of the Excel DATEDIF Function
A value which is representing the time difference between two dates
Logical Parameters Used in the Excel DATEDIF Function
- Unit: The unit of time to use in deriving the difference between the two dates (days, months of years).
- Start Date: The beginning date, entered in a recognizable Excel date format (serial number)
- End Date: The last date, entered in a recognizable Excel date format (serial number)
Figure 1: Example showing how to use the Excel DATEDIF function to calculate the difference in days between September 12, 2015, and December 12, 2018.
The formula used is:
And this returns 1187 as the days difference between the two dates in B8 and C8
Notes of Usage for the Excel DATEDIF Function
The Excel DATEDIF function (Date + Difference) can return the time difference between a first date and the last date either in days, years or months. The time unit that will be returned is dependent on the unit logical argument that is used. Using any of the formats below, the data unit can be provided as a text either in the lower or upper case.
While filling out the arguments for the Excel DATEDIF function, Excel won’t help you with auto-suggestion to function as other functions do, but it will give the perfect result when it is configured correctly.
General Notes regarding the use of the Excel DATEDIF Function
- The Excel DATEDIF function is designed to flag a #NUM error when the starting date is greater than the ending date.
- For more complex formula with an unknown start and end dates, or out of bounds, you can trap and manage any error using the Excel IFERROR function.