Calculates the number of days, months, or years between two dates. A hidden Excel function that provides flexible date difference calculations essential for age calculations, project tracking, and tenure analysis.
Master the fundamentals of Excel DATEDIF function
DATEDIF is a hidden Excel function that calculates the difference between two dates in various time units. Despite being undocumented in Excel help, it is fully functional and widely used for age calculations, project tracking, and tenure analysis. The function provides flexibility through different unit codes to calculate years, months, or days, with options to ignore certain components.
DATEDIF is not listed in Excel's function library but is fully functional. It works in all Excel versions but doesn't appear in AutoComplete.
Supports six unit codes: "Y" (years), "M" (months), "D" (days), "YM" (months ignoring years), "YD" (days ignoring years), "MD" (days ignoring months/years).
Calculates only complete periods. "Y" returns only full years passed, "M" returns only full months passed.
Start_date must be earlier than or equal to end_date. Violating this returns #NUM! error, ensuring logical date calculations.
Function-specific parameters
Function-specific return type
Calculate person age from birth date to current or specified date
Track project duration in years, months, or days for planning and reporting
Calculate employee tenure from hire date for HR and payroll calculations
Calculate contract duration and remaining time for legal and business purposes
Exact matching required
Returns numeric position
Handles missing text gracefully
=DATEDIF(start_date, end_date, unit)The start date. Must be earlier than or equal to end_date. Can be a date serial number, date string, or cell reference.
The end date. Must be later than or equal to start_date. Can be a date serial number, date string, or cell reference.
The unit of time to return: "Y" (years), "M" (months), "D" (days), "YM" (months ignoring years), "YD" (days ignoring years), "MD" (days ignoring months and years)
The number of time units between the dates
Description: Calculates the difference between two dates in various time units
Calculate complete years between two dates
Returns 4 complete years between the dates. Only counts full years that have passed.
Using DATEDIF function in VBA through WorksheetFunction
Sub DATEDIFExample()
Dim result As Long
Dim startDate As Date, endDate As Date
startDate = DateValue("2020-01-01")
endDate = DateValue("2024-01-01")
' Calculate years
result = Application.WorksheetFunction.DatedIf(startDate, endDate, "Y")
Range("A1").Value = result
MsgBox "Years: " & result
' Calculate months
result = Application.WorksheetFunction.DatedIf(startDate, endDate, "M")
Range("A2").Value = result
' Calculate days
result = Application.WorksheetFunction.DatedIf(startDate, endDate, "D")
Range("A3").Value = result
End Sub
' Calculate age from birth date
Sub CalculateAge()
Dim birthDate As Date
Dim ageYears As Long, ageMonths As Long, ageDays As Long
birthDate = Range("A1").Value
ageYears = Application.WorksheetFunction.DatedIf(birthDate, Date, "Y")
ageMonths = Application.WorksheetFunction.DatedIf(birthDate, Date, "YM")
ageDays = Application.WorksheetFunction.DatedIf(birthDate, Date, "MD")
Range("B1").Value = ageYears & " years, " & ageMonths & " months, " & ageDays & " days"
End SubCalculate age from birth date
Track project duration in months
Calculate employee tenure from hire date
Calculate contract duration and remaining time
DATEDIF returns #NUM! error
=DATEDIF(B1, A1, "Y")Solution: Ensure start_date is earlier than or equal to end_date. DATEDIF requires start_date ≤ end_date. Swap the dates or use conditional logic if dates may be reversed.
Function returns error or unexpected result
=DATEDIF(A1, B1, "y")Solution: Use only valid unit codes: "Y", "M", "D", "YM", "YD", "MD". Unit codes are case-sensitive and must be in quotes. Check for typos in the unit parameter.
DATEDIF doesn't appear in function list
=DATEDIF(...)Solution: DATEDIF is intentionally hidden by Microsoft but still works. Type it manually - it won't appear in AutoComplete but will function correctly when entered.