The DATEDIF function is a built-in function that calculates the number of days, months, or years between two dates.
Function Syntax
=DATEDIF(start_date, end_date, unit)
To use the DATEDIF function, you will need to provide two dates, a start_date and an end_date, and specify the unit of time, as specified by a unit symbol, which measures the duration between the two dates in that unit.
| Unit | Returns |
| “Y” or “y“ | the number of complete years between the start and end date. |
| “M” or “m“ | the number of complete months between the start and end date. |
| “D” or “d“ | the number of days between the start and end date. |
| “YM” or “ym“ | ignores the year, and identifies only the month difference. |
| “YD” or “yd“ | ignores the year, and identifies only the day difference. |
N.B.
Only whole units (integers) are returned by the function.
The start_date must be ‘less than’ or ‘equal to’ the end_date otherwise a #NUM error will be returned.
Usage
Used to identify the complete units elapsed between two dates, typically useful to identify someone’s age in years from their birth date, or to calculate the number of days in a year (leap year or otherwise).
Examples
Here are some examples to illustrate how the DATEDIF function works:
Example 1:
Calculate the number of elapsed years between January 1, 2020 and January 1, 2021.
=DATEDIF("1/1/2021", "1/1/2022", "y")
Output: 1
Example 2:
Calculate the number of elapsed months between January 1, 2020 and January 1, 2021, and will return the result “12” (twelve months).
=DATEDIF("1/1/2020", "1/1/2021", "m")
Output: 12
Example 3:
Calculate the number of (elapsed) days between January 1, 2020 and January 1, 2021, (note that 2020 was a leap year)
=DATEDIF("1/1/2020", "1/1/2022", "d")
Output: 366
Example 4:
A more unusual use case would be to calculate the difference between December 1 (ignoring the year) and January 1 (ignoring the year)
(Note: Dates are in US date format, where Excel is using the US date locale)
=DATEDIF("12/1/2020", "1/1/2023", "ym")
Output: 1
Even though there is over 1 year between the start and end dates, in actual duration.
Other Date Representations
Microsoft Date values
N.B. Dates can be represented as ‘date’ number values using the equivalent Microsoft date representation (eg. “01/01/2020” can be represented as the number 43831, and 01/01/2021 by 44197 in the DATEDIF function) , where the date is a sequential value where by default, January 1, 1900 is serial number 1
Example 5:
Such that Microsoft date representations can be used instead of string dates:
=DATEDIF(43831, 44197, "d")
Output: 366
Dates from Functions
Alternatively, dates can also be represented by other date functions such as DATEVALUE:
=DATEVALUE("2020-01-01")
or DATE:
=DATE(2020,01,01)
Both of which resolved to Microsoft Date values and therefore enable the DATEDIF function to evaluate as before.
Example 6:
=DATEDIF(DATE(2020,1,1), DATEVALUE("2021-01-01"), "d")
Output: 366
Related Topics
- Other Excel Functions
- Other Excel Date and Time functions
F011