YEARFRAC Function

Returns the fraction of a year between two dates.

Category

Date & Time

Available in

All versions, Microsoft 365

Returns

A decimal number of years

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Dates & Times module

Overview

YEARFRAC returns how much of a year lies between two dates — 0.5 for six months. Use it to prorate salaries, bonuses, rent or interest.

The basis argument picks the day-count rule: 30/360 (the default) or the real number of days.

Syntax

=YEARFRAC(start_date, end_date, [basis])

Arguments

ArgumentRequired?What it does
start_dateYesThe first date.
end_dateYesThe second date. The order doesn't matter — the result is always positive.
basisOptional0 or omitted US 30/360, 1 actual/actual, 2 actual/360, 3 actual/365, 4 European 30/360.

Examples

Example 1 — Same dates, different day counts

January 1 to July 1, 2026 under each basis.

B2fx=YEARFRAC(DATE(2026,1,1),DATE(2026,7,1))
AB
1BasisFraction
20 (30/360)0.5000
31 (actual/actual)0.4959
42 (actual/360)0.5028
53 (actual/365)0.4959

181 real days divided by 365 gives 0.4959; the 30/360 rule counts exactly six 30-day months.

Example 2 — Prorated bonus for a mid-year starter

A $12,000 annual bonus for someone who started on 7/1/2026, paid to 12/31/2026.

D2fx=C2*YEARFRAC(A2,B2,1)
ABCD
1StartYear endAnnual bonusProrated
27/1/202612/31/2026120006016.44
=C2*YEARFRAC(A2,B2,1) Explain

Common mistakes

  • Small differences from a manual calculation. Check the basis. Leaving it out uses 30/360, which rarely matches a simple days ÷ 365.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.