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.
⚠️ Time values in the start_date or end_date will be disregarded. Use the TIMEDIF Google Sheets add-on to calculate the duration between two times.
Related functions
DAYS – Simple duration 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, or input is a number that can’t be converted to a date.
#VALUE – An input is not a numerical value that can be changed to a 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 datesYM– 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 datesMD– Number of days elapsed after the number of months shown with the “M” or “YM” unit. TheMDvalue can’t go higher than 30.
D– Number of whole days elapsed between start and end dates
Examples
The data in this spreadsheet is used for all the examples.
Example 1 – Days Between Two Dates
First, we’ll look at a simple example of calculating the days between two dates.
| Formula | Result |
=DATEDIF("March 1,2017","March 3, 2017","D") | 2 |
This function returns 2 days. Let’s look at these two days as a visual.
You can see that from March 1, 2017, to March 2, 2017, is one day, and from March 2, 2017, to March 3, 2017, is another. While it may seem obvious, it is important to understand this because some sites describe this function as returning the days between two dates. This could be interpreted as one day in this example – March 2, 2017.
However, this formula is the same as using simple subtraction: ="3/3/17"-"3/1/17". Next, we will look at examples where you need DATEDIF because subtraction cannot give you the desired output.
Example 2 – Years, Months, and Days Between Two Dates
Getting the correct output from the DATEDIF function requires careful use of the unit argument. If you want years, months, and days for the time difference, you must use the Y, YM, and MD designations for the unit in three separate functions.
The Y argument produces the number of years elapsed. Additionally, YM pairs well with Y by considering only the months after the last whole year. Similarly, MD only finds the days after the last whole month.
In row 2, we see that Matilda is 17 years, 7 months, and 5 days old on August 28, 2016.
Example 3 – Months and Days Between Two Dates
Next, let’s say you want months and days but not years. In this case, the months can be greater than 11, and you use the M and MD designations for the unit argument.
Let’s look at row 2 in this example. Matilda is 211 months old on August 28, 2016. We get this number by using M for the unit argument. M is designed to output total months instead of months after the last year. Then, MD provides 5 days remaining after the last whole month, so she is 211 months and 5 days old.
Example 4 – Years and Days Between Two Dates
Next, let’s combine the Y unit with YD to return the years and days. The YD function returns the number of days after the first full year, which cannot exceed 364 or 365 on leap years.
This example shows us that Matilda is 17 years and 217 days old on August 28, 2016. Now, let’s combine those numbers into a format that is easier to read. We could combine this with the CONCATENATE function or use the & operator as a shortcut. We will use the & operator. This operator makes the formula more straightforward to write than CONCATENTATE.
Example 5 – DATEDIF’s Shortcomings
DATEDIF has some odd outputs when the days in the starting month are less than those in the ending month. Let’s look at a few examples. Let’s compare the output to the YEARFRAC function. YEARFRAC can help you avoid these issues, but the output is a fraction of a year, not years, months, or days.
DATEDIF returns 2 months in row 2 for a period that most would consider to be 3 months because all of February, March, and April elapsed between the two dates. YEARFRAC correctly shows the time period to be .25, or a quarter of a year.
Row 3’s results make more sense as there are 2 months elapsed, but row 4 shows the same problem. The issues is caused by April having fewer days than January and March.
A similar issue can happen when counting years if you start with a leap day. Row 7 would make more since if the output was 1 year.
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.
Video Tutorial
Related Posts
Count cells with dates between two days
TIMEDIF Add-On – Find Durations in hours, minutes, and seconds