How To SheetsHow To Sheets
Formulas and Functions

DATEDIF in Google Sheets: Practical Date Differences

Learn how to use DATEDIF in Google Sheets to compute date differences in days, months, and years. This practical guide covers syntax, examples, edge cases, and alternatives like YEARFRAC and NETWORKDAYS for robust date calculations.

How To Sheets
How To Sheets Team
·5 min read
Calendar-Google-SheetsWhat-Is-Google-SheetsFormula-Errors-Google-Sheets
DATEDIF in Sheets - How To Sheets
Quick AnswerDefinition

DATEDIF in Google Sheets is a legacy date-difference function that computes how far two dates are apart in days, months, or years. It supports units like D, M, Y and specialized forms such as YM, YD, and MD for nuanced calculations. Example: =DATEDIF(A2,B2,"D").

Understanding datedif google sheets and when to use it

Date arithmetic is a core skill in spreadsheets, and datedif google sheets is a compact way to measure the gap between two dates. The function originated as a compatibility feature across spreadsheet programs and remains a reliable option for quick calculations where you need exact differences in days, months, or years. According to How To Sheets, datedif google sheets is still a go-to tool for age calculations, project durations, and scheduling templates because of its straightforward syntax and predictable results. This section lays the groundwork with the basic concept and a few starter formulas you can reuse across different datasets. Use a simple dataset where A2 contains a start date and B2 an end date to see how the function behaves across units.

Excel Formula
=DATEDIF(A2, B2, "D")
Excel Formula
=DATEDIF(A2, B2, "M")
Excel Formula
=DATEDIF(A2, B2, "Y")

Notes:

  • The start date should generally be earlier than or equal to the end date to avoid errors.
  • The unit codes (D, M, Y) control the granularity of the result.

wordCountTitle$2@1

tip - Start with a known date pair to experiment with each unit.

Steps

Estimated time: 60-90 minutes

  1. 1

    Prepare your data

    Ensure you have two date columns (start_date and end_date) or a birthdate and a reference date. Normalize dates with DATE or DATEVALUE to avoid misinterpretation due to locale.

    Tip: Use ISO-like dates (YYYY-MM-DD) when possible to minimize locale issues.
  2. 2

    Choose your unit

    Decide whether you need days (D), months (M), or years (Y). For more nuanced diffs, learn YM, YD, and MD.

    Tip: YM ignores years when counting months; YD ignores years when counting days.
  3. 3

    Write the basic DATEDIF formula

    Enter a simple DATEDIF to validate the inputs. Start with a basic unit to confirm results before combining tokens.

    Tip: Always wrap date references in proper cell references, not hard-coded values.
  4. 4

    Create meaningful strings

    Concatenate multiple DATEDIF outputs to form readable strings like 'X years Y months.'

    Tip: Use the ampersand operator & to join text and results.
  5. 5

    Guard against invalid dates

    Add an IF guard to handle start_date after end_date and avoid #NUM! errors.

    Tip: A simple guard keeps dashboards robust.
  6. 6

    Explore alternatives

    Compare DATEDIF with YEARFRAC and NETWORKDAYS for extended analyses in dashboards.

    Tip: YEARFRAC gives fractional years, useful for long-term forecasts.
Pro Tip: Always validate date inputs with a quick TYPE or ISDATE-style check if your data comes from user input.
Warning: DATEDIF is a compatibility function; its help text may be sparse in some environments, so rely on reputable tutorials for syntax.
Note: When using TODAY(), your results can update automatically as days pass, which is useful for ongoing reports.

Prerequisites

Required

Optional

  • Sample dataset containing start_date and end_date fields
    Optional

Keyboard Shortcuts

ActionShortcut
CopyCopy selected cell or range containing a DATEDIF formulaCtrl+C
PastePaste to destination cell to apply resultsCtrl+V
Fill downDrag or fill down to apply the formula to adjacent rowsCtrl+D
Insert current dateEnter today's date as a start or end date in DATEDIF calculationsCtrl+;
Insert current timeCapture time for timestamped date calculationsCtrl+⇧+;

FAQ

Is DATEDIF officially supported in Google Sheets formulas?

DATEDIF is a long-standing compatibility function in Sheets. It isn’t listed in the function wizard everywhere, but it works reliably across most Sheets versions. Always test in your environment to confirm syntax.

DATEDIF is a compatibility function that works in most Sheets setups, even if it isn’t prominently listed in help menus.

What units can I use with DATEDIF other than D, M, and Y?

In addition to D, M, and Y, DATEDIF supports YM, YD, and MD. These allow you to calculate differences while ignoring years or months, depending on the unit you choose.

You can use YM, YD, or MD to tailor the difference to your needs.

How do I handle a start date that is after the end date?

If start_date is later than end_date, DATEDIF will typically return an error. Use an IF guard to swap dates or return a friendly message.

If the start is after the end, check your dates and guard the formula with an IF statement.

Can DATEDIF calculate business days or exclude weekends?

DATEDIF does not exclude weekends by itself. For business-day differences, pair it with NETWORKDAYS or NETWORKDAYS.INTL for more control.

For business days, use NETWORKDAYS with your DATEDIF calculations where needed.

What are practical alternatives to DATEDIF for date differences?

YEARFRAC provides fractional years, useful for tenure estimates. NETWORKDAYS calculates business days between dates. Depending on your dashboard, these can complement DATEDIF nicely.

Consider YEARFRAC for fractional years and NETWORKDAYS for business-day counts as complements to DATEDIF.

The Essentials

  • DATEDIF computes date differences using D/M/Y units
  • YM/YD/MD let you ignore specific components for flexible calculations
  • Guard against invalid date orders to avoid #NUM! errors
  • YEARFRAC and NETWORKDAYS are solid complements to DATEDIF

Related Articles

← More in Formulas and Functions