The DATEDIF function is used to calculate the difference between two dates. It can return the difference in years, months, or days.
It is commonly used for:
- Age calculation
- Service duration
- Project timelines
- Contract periods
Structure of DATEDIF
=DATEDIF(start_date, end_date, unit)
Arguments:
- start_date → Beginning date
- end_date → Ending date
- unit → Type of difference to calculate
Units Used in DATEDIF
| Unit | Meaning |
|---|---|
| “Y” | Complete years |
| “M” | Complete months |
| “D” | Total days |
| “YM” | Months ignoring years |
| “YD” | Days ignoring years |
| “MD” | Days ignoring months and years |
Example 1: Calculate Age (Years)
If Date of Birth is in A1:
=DATEDIF(A1, TODAY(), "Y")
This calculates complete years (age).
Example 2: Calculate Total Months
=DATEDIF(A1, TODAY(), "M")
Returns total completed months.
Example 3: Calculate Total Days
=DATEDIF(A1, TODAY(), "D")
Returns total number of days between two dates.
Example 4: Detailed Age (Years, Months, Days)
You can combine formulas:
=DATEDIF(A1, TODAY(), "Y") & " Years, " &
DATEDIF(A1, TODAY(), "YM") & " Months, " &
DATEDIF(A1, TODAY(), "MD") & " Days"
This gives a complete age breakdown.
Important Notes
- Start date must be earlier than end date.
- DATEDIF is a hidden function (not listed in suggestions).
- Dates must be valid Excel dates (not text).
Why DATEDIF is Important
It helps you:
- Calculate age accurately
- Measure employee experience
- Track project durations
- Manage deadlines
- Analyze time-based data
The DATEDIF function is a powerful tool for calculating date differences in Excel efficiently and accurately.