Mastering Google Sheets DATEDIF for Date Differences
A practical, developer-focused guide to using DATEDIF in Google Sheets to compute date differences in days, months, and years with robust examples and best practices.
DATEDIF is a legacy function in Google Sheets that computes the difference between two dates. It supports units like D, M, and Y (and their variations) to return days, months, or years. Although not listed in the function wizard, you can use it by typing =DATEDIF(start_date, end_date, unit).
What is DATEDIF and when to use it in Google Sheets
Google Sheets, including the keyword google sheets datedif, still supports a compact, reliable way to measure time between two dates: the DATEDIF function. DATEDIF computes differences in days, complete months, or complete years, and it also offers partial-unit variants such as MD, YM, and YD for more nuanced calculations. This makes it ideal for age calculations, project durations, or elapsed time dashboards where you want integer results rather than decimal years. While modern date utilities like YEARFRAC or NETWORKDAYS exist, DATEDIF remains a concise option when you need exact whole units or a quick composite view of duration. The pattern is simple: =DATEDIF(start_date, end_date, unit). The start_date must be on or before the end_date to avoid an error, and the unit determines how the difference is measured. With careful handling of input types and a few guard rails, DATEDIF blends cleanly into Sheets formulas and can power robust date logic across rows and dashboards.
Code examples:
=DATEDIF(DATE(2020,1,1), DATE(2021,1,1), "D")=DATEDIF(A2, B2, "Y")# Parallel demonstration in Python (optional, for cross-tool learning)
from datetime import date
start = date(2020, 1, 1)
end = date(2021, 1, 1)
delta_days = (end - start).days
print(delta_days) # 366 on a leap-year-inclusive spanNotes:
- DATEDIF syntax is straightforward, but the two dates must be valid date values.
- The unit string must be exactly one of the accepted values: "D", "M", "Y", "MD", "YM", or "YD".
- For January and December boundaries, consider cross-checking with YEARFRAC if you need partial years.
contextAllowDuplicatesCheckersNoteFormatOnlyForMarkdownsAndExplanations
Steps
Estimated time: 15-25 minutes
- 1
Prepare date columns
Enter or ensure start dates in column A and end dates in column B. Confirm cells are formatted as date types (e.g., 2024-01-01).
Tip: Use a header row to label columns for clarity. - 2
Apply DATEDIF for a base unit
In C2, enter =DATEDIF(A2,B2,"D"). This returns the number of days between the two dates. You can switch the unit to YM, MD, or Y as needed.
Tip: Drag the fill handle to apply to additional rows. - 3
Compute other units
Add separate columns for months (unit "M") and years (unit "Y") to compare durations in different units. You can also combine variants like MD, YM, and YD for partial calculations.
Tip: Keep a readable column order: Days, Months, Years, Partial-terms. - 4
Validate results
Check for edge cases where the start date is after the end date; consider using IFERROR to handle #NUM! results gracefully.
Tip: A dashboard-friendly approach is IFERROR(DATEDIF(...), 0) or a custom message.
Prerequisites
Required
- Required
- Basic knowledge of date formats and built-in functionsRequired
Optional
- Access to Google Sheets (web) or mobile appOptional
- Familiarity with common date functions like DATE, DATEVALUE, NOWOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells | Ctrl+C |
| PastePaste copied cells | Ctrl+V |
| Fill downFill down formula to adjacent cells | Ctrl+D |
| UndoUndo last action | Ctrl+Z |
FAQ
What is DATEDIF and why use it in Google Sheets?
DATEDIF computes the difference between two dates in a specified unit. It’s useful for age calculations, project durations, and elapsed time in Sheets.
DATEDIF measures how much time passes between two dates in a chosen unit.
Which units can I use with DATEDIF?
Common units are D (days), M (months), and Y (years). There are also MD, YM, and YD variants for partial calculations.
You can calculate days, months, or years, plus partial unit variants.
Why might I see #NUM! in DATEDIF results?
#NUM! occurs when the start date is after the end date or if dates are invalid. Ensure proper date inputs and consider IFERROR.
When dates are out of order or invalid, DATEDIF returns #NUM!
Can I use DATEDIF with TODAY() to get age?
Yes. Use =DATEDIF(birthDate, TODAY(), 'Y') to get age in years, and combine with 'YM' for months after the last birthday.
Combine birth date with TODAY() to get age in years and months.
Is DATEDIF supported in Google Sheets help docs?
DATEDIF is a legacy function that works in Sheets even if not listed in the function picker. Use it directly by typing the syntax.
DATEDIF works in Sheets even if not in the help list.
The Essentials
- Use DATEDIF for precise date differences
- Match unit to your result needs (D, M, Y, or partials)
- Handle invalid inputs with IFERROR
- Combine DATEDIF with YEARFRAC for partial-year insights
- Validate formats before calculations