< Go Back

Excel DATEDIF Function

Formula

=DATEDIF(start_date,end_date,“unit”)

Overview

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)

Example

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:

=DATEDDIF(B6,C6,”d”)

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

  1. The Excel DATEDIF function is designed to flag a #NUM error when the starting date is greater than the ending date.
  2. 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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar