The DATEDIF function calculates the difference between two days. You can return the difference in days, months, years, or combinations. 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 cell reference. 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.
Contents
Related functions
DAYS – Simple duration of time formula with just days.
MINUS – Subtract one value from another. You can use this function with dates.
Errors
#NUM – The starting date is after the beginning date.
Purpose
The DATEDIF function returns the number of days, months, or years between two dates.
Syntax
=DATEDIF(start_date,end_date,unit)
start_date The date at which to start the calculation
end_date The date 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, it starts counting after the end of the previous 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. The MD value 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
Above are examples of using DATEDIF 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.