DATEDIF vs YEARFRAC: Ages and Years Between Dates

The short answer to DATEDIF vs YEARFRAC: DATEDIF counts complete years, months or days, and YEARFRAC returns the years as a decimal. Use DATEDIF when you need an age or "6 years, 5 months" of service. Use YEARFRAC when you need the fraction, for example to prorate a bonus. This guide is for anyone in HR, payroll or admin who works with start dates, birthdays and contract dates.

DATEDIF vs YEARFRAC at a glance#

DATEDIF answers "how many whole years?", and YEARFRAC answers "how many years, exactly?".

How the syntax differs#

Both functions take a start date and an end date. DATEDIF then needs a unit in quotes, and YEARFRAC takes an optional basis number.

=DATEDIF(start_date, end_date, unit)
=YEARFRAC(start_date, end_date, [basis])

DATEDIF units:

• "Y": complete years.
• "M": complete months.
• "D": days.
• "YM": months left over after the complete years.
• "YD": days left over after the complete years, ignoring the year.
• "MD": days left over after the complete months. Microsoft warns this unit can give wrong results, so avoid it.

YEARFRAC's basis sets the day-count rule: 0 or blank is US 30/360, 1 is actual/actual, 2 is actual/360, 3 is actual/365 and 4 is European 30/360. For HR and everyday business work, use 1, which counts real days and real year lengths. The YEARFRAC guide explains every basis in detail, and the DATEDIF function guide covers each unit.

The same dates, side by side#

Here are four employees, with an as-of date of 10 Sep 2025:

These are the results of four formulas in row 2, filled down:

DATEDIF tells you Ana has worked 6 complete years and 5 more months. Join the two for a readable label:

=DATEDIF(B2,C2,"Y")&" years, "&DATEDIF(B2,C2,"YM")&" months"

The result is 6 years, 5 months. YEARFRAC tells you the same service is 6.49 years, which you can multiply, average or chart. The last column uses the default basis 0 (30/360), so its results differ slightly from basis 1.

Which is better for age: DATEDIF or YEARFRAC?#

For ages, DATEDIF with "Y" is the safer choice, because it counts a year only when the birthday has been reached. Many people write =INT(YEARFRAC(B2,C2,1)) instead, and it usually gives the same number. Exactly on a birthday it can be one short.

Take someone born on 4 Sep 1988, checked on 4 Sep 2021, their 33rd birthday:

=DATEDIF(DATE(1988,9,4),DATE(2021,9,4),"Y")
=INT(YEARFRAC(DATE(1988,9,4),DATE(2021,9,4),1))

DATEDIF returns 33. YEARFRAC with basis 1 returns about 32.998, because over many years it divides the days by an average year length, so INT gives 32. The default basis has its own trap: for someone born on 31 May 1990, =YEARFRAC(DATE(1990,5,31),DATE(2025,5,30)) returns exactly 35, a day before the 35th birthday, while DATEDIF correctly returns 34.

For a rule like "must be 18 or older", one year off is a real error. The guide on calculating age and tenure in Excel shows full age formulas with today's date.

When to use DATEDIF#

Use DATEDIF when the answer must be in whole units. It is the right choice for:

• Ages and age checks, such as eligibility at 18 or 65.
• Tenure in years and months, like "6 years, 5 months" on a staff list.
• Service milestones, such as flagging everyone with 10 or more complete years.
• Complete months between two dates, for example months on a contract with "M".

DATEDIF needs the earlier date first. If the dates could be blank or reversed, guard it: =IF(C2>=B2,DATEDIF(B2,C2,"Y"),""). If you only need the number of days, subtraction or DAYS is simpler; see DAYS vs DATEDIF.

When to use YEARFRAC#

Use YEARFRAC when the part of a year matters. It is the better choice for:

• Prorating pay or bonuses. An annual bonus of 3,000 for someone who joined on 1 Apr 2025, measured to 31 Dec 2025: =3000*YEARFRAC(DATE(2025,4,1),DATE(2025,12,31),1) returns 2,252.05 (274 days out of 365).
• Average tenure across a team, where 6.49 is more accurate than 6.
• Interest and finance, where a contract names a day count like 30/360. Match the basis to the contract.
• Charts of service length, which look smoother with decimals.

To show a rounded decimal, wrap it: =ROUND(YEARFRAC(B2,C2,1),2) gives 6.49 for Ana.

Common mistakes with DATEDIF and YEARFRAC#

These slips cause most wrong answers:

1. Dates in the wrong order in DATEDIF. A start date after the end date gives #NUM!.
2. Forgetting the quotes around the unit. =DATEDIF(B2,C2,Y) gives #NAME?. Write "Y".
3. Using INT(YEARFRAC) for exact ages. It can be a year short on a birthday, as shown above.
4. Leaving out the basis. YEARFRAC's default is 30/360, not real days. Add 1 unless a policy says otherwise.
5. Dates stored as text. If a date sits on the left of the cell, Excel may see text and return #VALUE!. Convert it with DATEVALUE.
6. Trusting "MD". Microsoft warns that this DATEDIF unit can return wrong values. Calculate leftover days another way.

If you work with deadlines rather than past dates, see how many days until a deadline, and for plain day counts, how to count days between dates.

Our recommendation#

Use DATEDIF whenever you need whole years or months: ages, eligibility and tenure labels. Use YEARFRAC with basis 1 whenever you need the fraction: prorated pay, averages and decimal service. Don't turn YEARFRAC into whole years with INT for anything that has a legal or policy edge, such as an age limit.

In short: DATEDIF for "how many complete years", YEARFRAC for "how much of a year".

Practise it#

Try the YEARFRAC exercise on this page, then switch to whole years with DATEDIF in the days between dates lesson. You type each formula in a live spreadsheet and it is checked instantly, from simple day counts to tenure in years and months.