How to Calculate Age and Date Difference in Google Sheets (DATEDIF Guide)

Published on | ProSheetHub

Calculating the exact time elapsed between two dates is a fundamental requirement for HR employee records, project management, and customer account milestones. Whether you need to calculate someone's current age, employee tenure, or how many months remain on a contract, subtracting one date from another directly only yields total days.

To break that difference cleanly into complete years, months, and remaining days, Google Sheets provides a powerful hidden function: DATEDIF. In this guide, you will learn the exact syntax, unit codes, and practical formulas to calculate age and date intervals dynamically.

1. What Is the DATEDIF Function?

DATEDIF stands for Date Difference. It calculates the elapsed duration between a starting date and an ending date based on the specific time unit you define.

Formula Syntax

=DATEDIF(start_date, end_date, "unit")
  • start_date: The earlier date (e.g., birthdate, hire date, or project kickoff).
  • end_date: The later date (must be equal to or greater than the start_date).
  • unit: The text code (enclosed in double quotes) indicating the measurement format you want to return.
Important Order Rule: The start_date must always come before the end_date chronologically. If your start date is later than your end date, Google Sheets will throw a #NUM! error.

2. Understanding DATEDIF Unit Codes

The unit code tells Google Sheets exactly how to measure the gap between your dates:

Unit Code Description Example Use Case
"Y" Total completed full years Calculating age or annual work anniversaries
"M" Total completed full months Subscription length or lease tracking
"D" Total elapsed days Overall project duration
"YM" Remaining months after full years are subtracted Expressing tenure like "3 Years, 5 Months"
"MD" Remaining days after full months are subtracted Fine-grained day counts

3. How to Calculate Current Age Dynamically

To determine someone's age today without ever having to manually adjust the number every year, pair DATEDIF with the TODAY() function.

Assuming the birthdate is stored in cell A2:

=DATEDIF(A2, TODAY(), "Y")

Because TODAY() updates automatically every time your sheet opens or recalculates, your age column will remain 100% accurate indefinitely.

4. How to Calculate Detailed Tenure (Years & Months)

When generating employee records or tenure certificates, showing just "4 years" often misses important details. You can combine "Y" and "YM" strings using concatenation (&) to create human-readable strings:

=DATEDIF(B2, TODAY(), "Y") & " Years, " & DATEDIF(B2, TODAY(), "YM") & " Months"

If an employee was hired 3 years and 7 months ago, the cell outputs: 3 Years, 7 Months.

5. How to Handle Empty Date Cells

If your date column contains blank rows, DATEDIF will evaluate the blank cell as day 0 (December 30, 1899), outputting an incorrect number like 126.

Wrap your calculation inside an IF check to keep blank rows clean:

=IF(A2="", "", DATEDIF(A2, TODAY(), "Y"))

Troubleshooting Common DATEDIF Errors

Error Cause Solution
#NUM! The start_date is greater than the end_date. Swap the order of the two dates inside the formula.
#VALUE! One of the date cells is formatted as plain text instead of a valid date. Ensure the cells contain true date serials via Format > Number > Date.
#NAME? Misspelled function name or omitted quotes around the unit code. Verify spelling is DATEDIF and the unit is quoted: "Y", not Y.

Recommended Next Steps

Accurate date mathematics makes administrative and tracking sheets significantly more useful. Continue optimizing your spreadsheets with these relevant guides:

Comments

Post a Comment