Excel like a finance pro.
← The libraryPractice · 3 questions →
Date function
YEARFRAC
Returns the fraction of a year between two dates.
CommonDifficulty 1200 · ProficientUsage rank #165 of 520
When to use it
The fraction of a year between two dates under a day-count basis. Prorating annual amounts, accrued interest, and exact ages.
The shape of it
- Syntax
=YEARFRAC(start_date, end_date, [basis])
Worked examples
Half a year, 30/360
=YEARFRAC(DATE(2025,1,1),DATE(2025,7,1)) → 0.5
Basis 0 (30/360): six 30-day months.
Actual days
=YEARFRAC(DATE(2025,1,1),DATE(2025,7,1),1) → 0.495890411
Basis 1 (actual/actual): 181 of 365 days.
Age
=INT(YEARFRAC(DATE(1990,5,20),TODAY())) → 36
Age in whole years.
Worth knowing
- Prorated annual fee: fee × YEARFRAC(start,end,1).
- Basis 1 is the intuitive one; 0 is the default for legacy reasons.
- Order of the dates does not matter; the result is always positive.
Where it goes wrong
- #NUM! for a basis outside 0 to 4.
- Basis 0 gives 0.5 for Jan 1 to Jul 1 even in a leap year, which surprises people expecting actual days.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.