DATEDIF
The DATEDIF function calculates the number of complete years, months, or days between two dates.
This function can be useful when calculating work experience, age, time remaining until project completion, or the duration of events, taking into account calendar features.
Syntax
DATEDIF(start_date, end_date, unit)
| Argument | Description | Permitted values |
|---|---|---|
| start_date | Start date of the period | Date in numerical format or reference to a cell containing the date |
| end_date | End date of the period | Date in numerical format or reference to a cell containing the date |
| unit |
| Text code: “Y”, “M”, “D”, “YM”, “YD”, “MD” |
Examples of use
Calculation of age in years, months, and days
For the date of birth in cell C1 (May 10, 1985) and the current date:
=DATEDIF(C1, TODAY(), "Y") & " years, " & DATEDIF(C1, TODAY(), "YM") & " months, " & DATEDIF(C1, TODAY(), "MD") & " days"
Result: text in the format “X years, Y months, Z days” depending on the current date.
Calculation of full years between dates
If the start date is in cell A1 (03/15/2018) and the end date is in cell B1 (11/20/2023):
=DATEDIF(A1, B1, "Y")
Result: 5.
Calculation of full months between dates
The values are the same:
=DATEDIF(A1, B1, "M")
Result: 68.
Calculation of the number of days between the dates from the second example
=DATEDIF(A1, B1, "D")
Result: 2076.
Notes
- If the start date is later than the end date, the function will return the #NUM! error.
- When performing calculations in the “MD” format, the day in the end date is required to be greater than the day in the start date, otherwise the function will return the #NUM! error.
- The codes “YM”, “YD”, “MD” return the difference in months or days without taking full years into account.
- The function takes leap years and different numbers of days in months into account in its calculations.