How to Use DATEDIF Function in Excel - Practical Guide

Learn to use the DATEDIF function to calculate the difference between two dates in years, months, or days. This powerful formula is essential for HR departments, project management, and age calculations, enabling you to automate date calculations and generate accurate time-span reports without manual counting.

Why This Matters

DATEDIF automates date calculations for payroll, benefits, and project tracking, saving time and eliminating manual counting errors.

Prerequisites

•Basic understanding of Excel cell references and formulas
•Two date values or cells containing dates to compare

Step-by-Step Instructions

Select Your Target Cell

Click on the empty cell where you want the DATEDIF result to appear, typically in your data worksheet.

Enter the DATEDIF Formula Syntax

Type the formula: =DATEDIF(start_date, end_date, "unit") where start_date is the earlier date and end_date is the later date.

Specify the Unit of Measurement

Choose your unit in quotes: "Y" for years, "M" for months, "D" for days, "MD" for days excluding months, "YM" for months excluding years, or "YD" for days excluding years.

Reference Your Date Cells

Replace start_date and end_date with actual cell references (e.g., =DATEDIF(A2,B2,"Y")) or type dates as TEXT("2024-01-15","YYYY-MM-DD").

Press Enter and Review Results

Press Enter to execute the formula; the cell will display the calculated difference as a number matching your selected unit.

Alternative Methods

Using YEARFRAC for Decimal Years

Use =YEARFRAC(start_date, end_date) to calculate the exact fraction of years between dates, useful for age calculations requiring decimals.

Simple Date Subtraction

Subtract dates directly with =(end_date - start_date) to get days; divide by 365.25 for approximate years or by 30.44 for approximate months.

Tips & Tricks

✓Always ensure dates are in proper date format; if Excel treats them as text, the formula will return an error.
✓Use absolute references ($A$1) when copying formulas down to prevent cell references from shifting unexpectedly.
✓The end_date must always be later than start_date, or DATEDIF returns a #NUM! error.

Pro Tips

★Combine DATEDIF with TODAY() function: =DATEDIF(A2,TODAY(),"Y") to instantly calculate current age or tenure from a birth/hire date.
★Use nested IF statements to display different units based on the time span (e.g., show months if less than 1 year, years if greater).
★Create a helper column with DATEDIF results, then use conditional formatting to highlight employees reaching milestone anniversaries.

Troubleshooting

Formula displays #NAME? error

DATEDIF may not be available in your Excel version or region. Try YEARFRAC or =INT((end_date-start_date)/365) as alternatives.

Dates appear as numbers (e.g., 45000) instead of formatted dates

Right-click the cells, select Format Cells > Date tab, and choose your preferred date format to display properly.

Formula works in one cell but shows errors when copied

Check that relative references (A2) adjust correctly when copied; use absolute references ($A$1) if the comparison date should stay fixed.

Related Excel Formulas

YEARFRAC TODAY DATE DAYS MONTHS YEAR

Frequently Asked Questions

What is the difference between DATEDIF and YEARFRAC?

DATEDIF returns whole numbers (years, months, or days) based on your unit choice, while YEARFRAC calculates the exact decimal fraction of years between dates, such as 2.5 years.

Can DATEDIF work with times, not just dates?

DATEDIF only recognizes the date portion and ignores times, so =DATEDIF(A2,B2,"D") will give the same result whether times are included or not.

Is DATEDIF available in Google Sheets?

No, DATEDIF is an Excel-specific function and does not work in Google Sheets; use DAYS() or (end_date-start_date)/365 as alternatives.

How do I calculate age in Excel using DATEDIF?

Use =DATEDIF(birth_date, TODAY(), "Y") where birth_date is in column A; this automatically calculates current age in complete years.