Calculating the duration between two dates—such as an employee’s exact age, service tenure, contract duration, or project milestone intervals—is a common requirement in human resources, project management, and financial planning. While subtracting two dates directly in Google Sheets (e.g., =End_Date - Start_Date) yields the total number of elapsed calendar days, converting those raw days into meaningful years, months, and remaining days requires complex modulo calculations. To simplify this calculation, Google Sheets includes the powerful, legacy-compatible DATEDIF function.
In this comprehensive guide, you will learn the syntax, unit parameters, practical formulas, and error handling methods for the DATEDIF function in Google Sheets.
What Is the DATEDIF Function?
The DATEDIF (“Date Difference”) function computes the difference between two date values and returns the result in units of complete years, complete months, or days.
Key highlights of DATEDIF include:
- Complete Period Rounding: Computes strictly completed calendar years or months, avoiding confusing fractional values.
- Excluded Interval Calculation: Supports specialized units (such as
"YM"and"MD") that calculate remaining months or days while ignoring completed years. - Dynamic Age Modeling: Pairs seamlessly with the
TODAY()function to calculate dynamically updating ages and milestone anniversaries.
Syntax and Argument Breakdown
The syntax for the DATEDIF function in Google Sheets is:
=DATEDIF(start_date, end_date, unit)Argument Descriptions
start_date(Required): The beginning date of the calculation. Must be earlier than or equal toend_date.end_date(Required): The conclusion date of the calculation.unit(Required): A text code enclosed in quotation marks specifying the desired measurement unit.
Available Unit Codes
| Unit Code | Description | Calculation Logic |
|---|---|---|
"Y" |
Complete Years | Number of whole years elapsed between dates. |
"M" |
Complete Months | Total number of whole months elapsed between dates. |
"D" |
Complete Days | Total number of calendar days elapsed between dates. |
"YM" |
Remaining Months (Ignoring Years) | Calculates months elapsed after subtracting full years. |
"YD" |
Remaining Days (Ignoring Years) | Calculates days elapsed assuming both dates fall in the same year. |
"MD" |
Remaining Days (Ignoring Months & Years) | Calculates days elapsed after subtracting full years and months. |
Practical Calculations and Formula Examples
1. Calculating Age in Completed Years
To calculate a person’s exact current age based on a birthdate stored in cell A2:
=DATEDIF(A2, TODAY(), "Y")If cell A2 contains 15/06/1995 and today is 14/10/2026, the formula returns 31.
2. Calculating Total Tenure in Months
To find the total number of whole months an employee has been with the company between their hire date (B2) and their departure date (C2):
=DATEDIF(B2, C2, "M")3. Formatting a Detailed Tenure String (“X Years, Y Months, Z Days”)
In HR management reports, stakeholders frequently expect a full, human-readable breakdown of service duration. You can concatenate multiple DATEDIF functions together:
=DATEDIF(A2, B2, "Y") & " Years, " & DATEDIF(A2, B2, "YM") & " Months, " & DATEDIF(A2, B2, "MD") & " Days"Example Output: "4 Years, 7 Months, 12 Days".
Using DATEDIF with ARRAYFORMULA
To compute ages across an entire dataset without manually dragging formulas down rows, combine DATEDIF with MAP and LAMBDA:
=MAP(A2:A100, LAMBDA(birthdate, IF(ISBLANK(birthdate), "", DATEDIF(birthdate, TODAY(), "Y"))))This automatically populates ages for populated cells while leaving blank rows clean.
Troubleshooting Common DATEDIF Errors
The #NUM! Error (Start Date Later Than End Date)
DATEDIF strictly requires that start_date <= end_date. If the start date is more recent than the end date (for example, comparing an upcoming project deadline against today), DATEDIF triggers a #NUM! error.
Solution: Verify date order, or wrap with IF to ensure chronological validity:
=IF(A2 > B2, "Invalid Date Range", DATEDIF(A2, B2, "Y"))The #VALUE! Error (Dates Formatted as Text)
If cells contain text strings rather than serial date numbers (e.g., imported from external CSV files with mismatched regional separators), DATEDIF returns a #VALUE! error.
Solution: Convert text dates into true date serial numbers using DATEVALUE:
=DATEDIF(DATEVALUE(A2), TODAY(), "Y")Unexpected Negative Results with the “MD” Unit
Due to variations in month lengths (28, 30, or 31 days), the "MD" unit can occasionally yield minor unexpected rounding discrepancies if the start date falls on the 31st of a month and the end date falls in a shorter month. In mission-critical financial calculations, consider using exact day arithmetic ("D") or YEARFRAC for proportional calculations.