What is the DATEDIF function?
The Excel DATEDIF function calculates the number of days, months or years between two dates.
The Excel DATEDIF function is an undocumented function, even though in newer Excel versions you can find the DATEDIF function documentation in the Excel help menu.
DATEDIF Function Syntax
DATEDIF(start_date,end_date,unit)
start_date, end_date, required, the two dates you want to calculate the difference between them
unit, required, determine the difference to be calculated, the number of days, months or years
- “d” calculates the number of days between two dates
- “m” calculates the number of complete months between two dates
- “y” calculates the number of complete years between two dates
- “yd” calculates the number of days, number of years ignored
(the remaining days are not enough for one year). - “ym” calculates the number of months, the number of years ignored
(the remaining months are not enough for one year) - “md” calculates the number of days, number of months ignored
(the remaining days are not enough for one month)
Usage Note:
- start_date must be smaller than end_date. Otherwise, it will generate a #NUM error.
- start_date and end_date are dates between January 1, 1900, to December 31, 9999, before or after the range will generate a #VALUE error
How to Use DATEDIF Function in Excel
For example, there are data such as the image below, how the results of the DATEDIF function in column C.
DATEDIF Function #1
=DATEDIF(A1,B1,"D")
The result is #VALUE! Error, start_date is less than January 1, 1900. As previously explained, Excel only able to process date from January 1, 1900, until December 31, 9999
DATEDIF Function #2
=DATEDIF(A2,B2,"D")
The result is #NUM! Error, both dates are in the range of January 1, 1900, until December 31, 9999, but start_date must be smaller than end_date, otherwise will generate a #NUM! Error.
DATEDIF Function #3
=DATEDIF(A3,B3,"d")
The 3rd to 8th functions all have the same start_date and end_date value, the difference is the unit arguments.
The 3rd function has a “d” unit argument, the result of the 3rd function is the different days between August 8, 1945, to September 30, 2018. There are 26,707 days between two dates.
DATEDIF Function #4
=DATEDIF(A4,B4,"m")
The 4th function has an “m” unit argument, calculate a number of complete months between two dates. The result is 877 a month.
If compared to the results of the 3rd function, the difference between the two dates is more than 877 months, but the remaining days are less than one month.
Excel only calculates the difference of the complete month and ignores the remaining days. The 5th function below calculates the remaining days that are not enough for one month.
DATEDIF Function #5
=DATEDIF(A5,B5,"md")
The 5th function has an “md” unit argument, calculate the not enough for 1-month remaining days. There are 13 days remaining; this is not enough to count to 1 month.
DATEDIF Function #6
=DATEDIF(A6,B6,"y")
The 6th function has a “y” unit argument, calculate a number of complete years between two dates. The result is 73 years.
The 4th function results are 877 months if divided by 12 the result is 73.08333.
The result is not an integer number; there is a decimal number, it means the remaining month is not enough for one year. Once again Excel only calculates the complete difference. Excel ignores the not enough for 1-year remaining months.
The next question, how much is the remaining months? The 7th function answers the question.
DATEDIF Function #7
=DATEDIF(A7,B7,"ym")
The 7th function has a “ym” unit argument, calculate the remaining months that are not enough for one year. There is one month remaining; this is not enough to count to 1 year.
DATEDIF Function #8
=DATEDIF(A8,B8,"yd")
The 8th function has a “yd” unit argument, calculate the remaining days that are not enough for one year. There are 44 days remaining; this is not enough to count to 1 year.
Please see the image below for the complete results of all functions above.
