If you want to find out how much time sits between two dates in Google Sheets, whether in years, months, or days, the DATEDIF function does it for you. You give it a start date, an end date, and a code for the unit you want back.
In this article, I’ll show you how to use each of the DATEDIF unit codes with simple, real-world dates so you can pick the right one for your situation.
DATEDIF Function Syntax in Google Sheets
The DATEDIF function returns the difference between two dates in the unit you choose.
=DATEDIF(start_date, end_date, unit)
- start_date is the earlier date.
- end_date is the later date. It must be on or after the start date.
- unit is a text code in quotes that sets what you get back: “Y”, “M”, “D”, “YM”, “YD”, or “MD”.
The unit codes mean: “Y” full years, “M” full months, “D” total days, “YM” months ignoring years, “YD” days ignoring years, and “MD” days ignoring months and years.
When to Use DATEDIF Function
- Working out a person’s age in whole years from a birth date.
- Counting how many full months a subscription or project has run.
- Getting the exact number of days between two dates.
- Building an age or tenure label like “3 years, 6 months”.
- Tracking time elapsed for contracts, memberships, or warranties.
Example 1: Difference in Full Years
Let’s start with the most common use, counting whole years between two dates.
Below is the dataset, with a start date in column A and an end date in column B, across rows 2 to 5.
I want the number of complete years between each pair of dates in column C.
Here is the formula:
=DATEDIF(A2, B2, "Y")
The “Y” code counts only full years that have passed between the two dates. A partial year doesn’t count until it’s complete. The first row returns 9.
Pro Tip: To work out someone’s current age, pair DATEDIF with TODAY as the end date: =DATEDIF(A2, TODAY(), “Y”). It recalculates every day so the age stays current.
Example 2: Difference in Full Months
If you need months instead of years, just swap the unit code.
Below is the dataset, with a start date in column A and an end date in column B, across rows 2 to 5.
I want the total number of complete months between each pair of dates in column C.
Here is the formula:
=DATEDIF(A2, B2, "M")
The “M” code counts every full month across the whole span, including the months inside any complete years. The first row gives 6 months.
Example 3: Difference in Days
When you want the raw count of days, the “D” code gives you the total.
Below is the dataset, with a start date in column A and an end date in column B, across rows 2 to 5.
I want the total number of days between each pair of dates in column C.
Here is the formula:
=DATEDIF(A2, B2, "D")
The “D” code counts every single day from the start date to the end date. The first row returns 30 days.
Pro Tip: For total days only, you can also just subtract the dates directly with =B2-A2. DATEDIF with “D” matters more once you combine it with the other unit codes below.
Example 4: Months Ignoring Full Years
The “YM” code is where DATEDIF gets useful for building readable labels. It gives the leftover months after the full years are taken out.
Below is the dataset, with a start date in column A and an end date in column B, across rows 2 to 5.
I want just the months remaining after counting whole years, in column C.
Here is the formula:
=DATEDIF(A2, B2, "YM")
How this formula works:
- “YM” first sets aside all the complete years between the dates.
- Then it counts only the months left over, always a number from 0 to 11.
- This is the piece you’d put after the years in an age label. The first row returns 6.
Example 5: Days Ignoring Full Months
The “MD” code finishes the set. It gives the leftover days after the full months are removed.
Below is the dataset, with a start date in column A and an end date in column B, across rows 2 to 5.
I want just the days remaining after counting whole months, in column C.
Here is the formula:
=DATEDIF(A2, B2, "MD")
The “MD” code strips out the full months and returns only the extra days on top. Put “Y”, “YM”, and “MD” together and you get a full “years, months, days” breakdown. The first row returns 15.
Tips & Common Mistakes
- DATEDIF doesn’t appear in the autocomplete list. Google Sheets supports it for compatibility but won’t suggest it as you type. You have to type the whole name yourself. It still works fine.
- The start date must come first. If the end date is earlier than the start date, DATEDIF returns a #NUM! error. Always put the earlier date in the first argument.
- The unit code needs quotes. Codes like “Y”, “M”, and “YD” must sit inside double quotes. Leaving them out gives a #ERROR! or #NAME? result.
That covers all six DATEDIF unit codes, from full years down to leftover days.
Once you know which code returns which piece, building age and tenure calculations is straightforward.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: