YEARFRAC in Excel: Part of a Year Between Two Dates

YEARFRAC in Excel returns the time between two dates as a fraction of a year, so six months comes back as 0.5 and four years and four months as about 4.33. Use it for tenure in decimal years, age with decimals, and prorating an annual salary, bonus or budget. This guide explains the syntax, which basis to pick, and how YEARFRAC differs from DATEDIF.

What does YEARFRAC do?#

YEARFRAC counts the days between two dates and divides by the length of a year. The result is a decimal number of years.

=YEARFRAC(start_date, end_date, [basis])

From January 1 to July 1, 2024, =YEARFRAC(A2,B2) returns 0.5. The order of the dates doesn't matter: YEARFRAC always returns a positive number. It works in every version of Excel.

How to use YEARFRAC step by step#

Here are four employees and the date of their last review:

1. Click D2 and type =YEARFRAC(.
2. Click B2 for the start date and type a comma.
3. Click C2 for the end date and type a comma.
4. Type 1 for actual days, close the bracket and press Enter.

=YEARFRAC(B2,C2,1)

Jordan's tenure is 4.33 years (shown to two decimals). Fill down with Ctrl+D on Windows or ⌘D on a Mac. If you see a long decimal, set the number of decimal places with Home > Decrease Decimal, or wrap the formula in ROUND.

Which basis should you use?#

The basis tells YEARFRAC how to count. There are five options:

For HR, age, tenure and most business work, use basis 1. It counts real calendar days and real year lengths, including leap years. When the dates span several years, it divides by the average year length over those years.

Bases 0 and 4 come from finance, where interest is often calculated on a 30/360 day count. They can give slightly different results from real days. From March 31 to June 30, 2025:

The differences are small, but they matter in payroll and contracts. If a policy names a day-count rule, match it. Otherwise, choose 1 and use it everywhere.

How to prorate a salary or bonus#

Multiply YEARFRAC by the annual amount to get the share for part of a year. A new hire starts on April 1, 2025 with an annual bonus target of 48,000, paid for the time worked up to December 31:

=ROUND(YEARFRAC(B2,C2,1)*D2,2)

April 1 to December 31 is 274 days, and 274 ÷ 365 = 0.7507, so the prorated bonus is 36,032.88. ROUND keeps it to cents. Check your own policy on whether the end date counts as a full day: if it should, add 1 to the end date, YEARFRAC(B2,C2+1,1).

Prorating comes up often in HR and payroll tasks. Our guide to Excel skills for HR jobs lists the other formulas those tests expect.

How to get whole years or round the result#

Wrap YEARFRAC in INT to drop the decimals and get completed years:

=INT(YEARFRAC(B2,C2,1))

For Jordan this returns 4. Use =ROUND(YEARFRAC(B2,C2,1),1) for one decimal place (4.3).

For age, the same pattern works with a birth date and TODAY():

=INT(YEARFRAC(B2,TODAY(),1))

TODAY() updates every day, so the age stays current. Our guide on calculating age or years of service compares this with DATEDIF and covers leap-day birthdays.

YEARFRAC vs DATEDIF#

Use DATEDIF for complete units and YEARFRAC for the fraction in between.

The DATEDIF function guide explains its units, such as "Y", "M" and "YM". If you just need the number of days between two dates, subtraction or DAYS is simpler; see how to count days between dates.

Common YEARFRAC mistakes#

These are the issues that cause wrong or surprising answers:

• Leaving out the basis. The default 30/360 count is fine for finance but can be off by a day or two of real time. Type 1 unless you need 30/360.
• Expecting whole years. YEARFRAC returns decimals. Use INT for completed years.
• Rounding up tenure. ROUND(4.6,0) gives 5, though the person has only 4 full years. Use INT for service-based rules.
• Text dates. YEARFRAC returns #VALUE! if a date is text that Excel can't read.
• Month-end edge cases with basis 0. 30/360 treats every month as 30 days, so spans that include the 31st or the end of February can differ from the real day count by a day or two.

YEARFRAC also pairs well with month boundaries. To prorate from the first of the month, use the first or last day of the month as your start or end date; to count down instead of measuring, see days until a deadline.

Practise it#

Try the tenure exercise on this page. Then open the days between dates lesson, where you measure tenure with subtraction, DAYS and DATEDIF in a live spreadsheet and see how each one counts.