The DATEDIF function calculates the difference between two days. The difference can be returned in day, month, year or a combination thereof. Whereas the DAYS function will only return days, this function has multiple output options.
The input for this function needs to be either surrounded by quotes or a reference to another cell with a date in it. If your days, months, and years are in different cells, use the DATE function to combine them. If you want to know the current age, use TODAY() as the end date.
Related functions
DAYS – Simple duration of time formula with just days.
MINUS – Subtract one value from another. Can be done with dates.
Errors
#NUM – The starting date is after the beginning date.
Purpose
The DATEDIF function returns the number of day, month, or years between two dates.
Syntax
=DATEDIF(start_date,end_date,unit)
start_date Date at which to start the calculation
end_date Date at which to end the calculation
unit Type of output. Choices are “Y”, “M”, “D”, “YM”, “YD”, or “MD”.
- Y – Number of whole years elapsed between start and end dates
- YM – Number of months elapsed after the number of years shown with the “Y” unit. Will not exceed 11.
- YD – Number of days elapsed after the number of years shown with the “Y” unit. No matter how many days after the last year, starts counting after end of last full year and is never over 364.
- M – Number of whole months elapsed between start and end dates
- MD – Number of days elapsed after the number of months shown with the “M” or “YM” unit. Can’t go higher than 30.
- D – Number of whole days elapsed between start and end dates
Examples
Example 1 – Plain and Simple
| Formula | Result |
| =DATEDIF(“March 1,2017″,”March 3, 2017″,”D”) | 2 |
This function returns two days, the same as if you did ="3/3/17"-"3/1/17".
Example 2 – Different outputs
Examples showing DATEDIF being used with different parameters.
Live examples in Sheets
Go to this spreadsheet for the examples of the DATEDIF function shown above that you can study and use anywhere you would like.