DATEDIF in Google Sheets: Calculate Days Between Dates

DATEDIF in Google Sheets calculates the difference between two dates in years, months, or days. It is the go-to function for calculating age from a birthdate, employee tenure, or the exact span between any two dates.

DATEDIF is inherited from Lotus 1-2-3 and is widely described as a hidden function. That is no longer true in Google Sheets: typing =DATEDIF brings up the autocomplete suggestion, and it sits in the Insert > Function > Date list between DATE and DATEVALUE. This guide covers all six DATEDIF units, walks through calculating tenure and age step by step, and shows how to handle the common errors.

In This Guide

• DATEDIF Syntax and Parameters
• How to Calculate Tenure with DATEDIF: Step-by-Step
• DATEDIF Examples
• Common Errors and How to Fix Them
• DATEDIF vs Subtraction: When to Use Which
• Tips and Best Practices
• Related Google Sheets Tutorials
• Frequently Asked Questions

DATEDIF Syntax and Parameters

The Six DATEDIF Units

The first three units (Y, M, D) give you the full span in a single scale. The last three (YM, MD, YD) give you the leftover portion after subtracting the larger unit. Together, they let you build a display like “7 years, 11 months, 5 days.”

How to Calculate Tenure with DATEDIF: Step-by-Step

We’ll use the employee database to calculate how long each person has worked at the company. The data has 8 employees with Name in column B, Hire Date in column D, and Birthdate in column E.

Select an empty cell and enter:

D2 contains Sherlock Holmes’s hire date (8/1/2018). The "Y" unit returns the number of complete years between that date and today. As of July 2026, this returns 7.

Enter two more DATEDIF formulas using the same start and end dates but with different units:

The "M" unit returns 95 total months. The "D" unit returns 2896 total days. Each unit gives you the same date span measured on a different scale.

The "YM" unit returns the remaining months after complete years. The "MD" unit returns the remaining days after complete months. Combine them with the ampersand operator (&) to build a text string:

This returns a string like “7 years, 11 months, 5 days” (the exact values depend on today’s date). Three separate DATEDIF calls feed into a single readable output.

For more on building text strings with the ampersand operator, see text functions in Google Sheets.

The same DATEDIF pattern works for age. Enter:

E2 contains Sherlock Holmes’s birthdate (7/31/1980). This returns 45 as of July 2026. Copy the formula down through all 8 employees.

Spot-check a few rows:

• Dr. John Watson (born 9/19/1979), age 46
• Sherlock Holmes (born 4/1/1978), age 48
• Mary Morstan (born 2/13/1981), age 45

Select the tenure formula cell and drag the fill handle down through row 9 to calculate tenure for all 8 employees. Do the same for the age formula. Each row references its own Hire Date and Birthdate, so the results update per employee. Dr. John Watson shows the longest tenure at 8 years (hired 9/1/2017).

DATEDIF Examples

Example 1: Days Until a Deadline

You have a project deadline in cell B2 and want to know how many days remain. Use TODAY() as the start date and the deadline as the end date:

If B2 contains 12/31/2026, this returns the total number of calendar days from today until December 31. Once the deadline passes, the formula returns a #NUM! error because TODAY() becomes the later date. Wrap it in IFERROR to handle that:

Example 2: Display “X Years, Y Months” for All Employees

For a report-ready tenure column, combine "Y" and "YM" into a formatted string:

Results for the employee database:

• Sherlock Holmes (hired 8/1/2018): 7 yr 11 mo
• Dr. John Watson (hired 9/1/2017): 8 yr 10 mo
• Dr. Mortimer (hired 1/10/2020): 6 yr 5 mo
• Mary Morstan (hired 3/22/2021): 5 yr 3 mo

The "Y" unit gives complete years. The "YM" unit gives the remaining months. Together they tell the full story without rounding.

Example 3: The YD Unit for Anniversary Tracking

The "YD" unit returns the number of days since the last anniversary of the start date. Use it to see how far into the current year of service an employee is:

For Sherlock Holmes (hired 8/1/2018), this returns the number of days since his last work anniversary on 8/1/2025. If today is July 6, 2026, the result is 339 days into his 8th year of service.

Common Errors and How to Fix Them

#NUM! Error

DATEDIF returns #NUM! when the start date is after the end date. This is the most common DATEDIF error.

This breaks because TODAY() (2026) comes after Sherlock Holmes’s hire date (2018). Swap the arguments:

If your data might have dates in either order (e.g., comparing two dates where either could be earlier), use IFERROR to catch the error:

This tries A2 as the start date first. If that produces a #NUM! error, it swaps and tries B2 as the start date instead.

#VALUE! Error

A #VALUE! error means one of the date arguments is not a valid date. The most common cause: the cell contains text that looks like a date but is not recognized by Google Sheets.

Test with =ISNUMBER(D2). If it returns FALSE, the cell is text. Fix it with =DATEVALUE(D2) to convert the text to a proper date, then use that converted value in your DATEDIF formula.

This happens frequently with dates imported from CSV files or pulled with IMPORTRANGE.

MD Unit Quirks

The "MD" unit (days remaining after complete months) can produce unexpected results near month boundaries. Google Sheets’ implementation occasionally returns negative numbers or miscounts when the start day is later in the month than the end day.

For example, DATEDIF from 1/31/2026 to 3/1/2026 with "MD" might return an unexpected value instead of the 1 day you would expect. If your results look wrong with "MD", cross-check with =DAY(end_date) - DAY(start_date) and adjust as needed.

DATEDIF vs Subtraction: When to Use Which

Subtracting two dates in Google Sheets gives you the number of days between them:

This is faster to type than DATEDIF with the "D" unit, and it works in both directions (negative result if A2 is later). So when do you need DATEDIF?

Use subtraction when:

• You need total days and nothing else
• You want the result to work regardless of date order (negative values are acceptable)
• You are feeding the result into another calculation

Use DATEDIF when:

• You need years, months, or a combined display (Y, M, YM, MD)
• You are calculating age from a birthdate where complete years matter
• You want “7 years, 6 months” instead of “2759 days”

Subtraction is a shortcut for day counts. DATEDIF is the right tool when you need to express the date difference in human-readable units.

Tips and Best Practices

1. Always use TODAY() as the end date for age and tenure. Hardcoding a date means your formula goes stale tomorrow. TODAY() recalculates every time the sheet opens.

2. Remember it only counts forward. DATEDIF needs the earlier date first. Put them the other way round and you get #NUM! rather than a negative number, which is the single most common way this function fails.

3. Combine Y and YM for the most useful display. Total months ("M") or total days ("D") are useful for calculations, but reports look better with “7 years, 6 months.” Use the combined formula pattern from Step 3.

4. Wrap DATEDIF in IFERROR when date order is uncertain. User-entered dates might be in the wrong order. =IFERROR(DATEDIF(A2, B2, "D"), "Check dates") prevents #NUM! errors from breaking your sheet.

5. Avoid the MD unit for precise calculations. The "MD" unit has known quirks near month boundaries. If you need days remaining after months, cross-check with =DAY(end_date) - DAY(start_date) to confirm accuracy. See adding and subtracting dates for alternative approaches.

Related Google Sheets Tutorials

• Date Functions: The Complete Guide: Overview of all date functions including TODAY, EDATE, NETWORKDAYS, and DATEVALUE
• TODAY and NOW Functions: Auto-updating current date and time, plus patterns for combining with DATEDIF
• How to Add and Subtract Dates: EDATE, date math, and working with date intervals
• How to Format Dates: Custom date formats, locale settings, and display options
• IF Statements in Google Sheets: Build conditional logic and IFERROR wrappers for date formulas