How to Calculate a Fraction of a Year with YEARFRAC in Excel

Prorate bonuses and vacation with YEARFRAC: the default 30/360 basis, actual/actual days, and rounding to half days.

You will learn to

• Return the fraction of a year between two dates
• Prorate an amount using actual days
• Round a prorated allowance to half days

How it works

YEARFRAC(start_date, end_date, [basis]) returns the fraction of a year between two dates — 0.5 means half a year. It is the cleanest way to prorate an annual amount such as a bonus, a salary or a vacation allowance for someone who joined mid-year.

The basis decides how days are counted: 0 (default) is US 30/360, 1 is actual days / actual year length, 2 is actual/360, 3 is actual/365 and 4 is European 30/360. For people-related proration, basis 1 is usually the fairest because it counts real calendar days.

=YEARFRAC(B2,$I$1,1)