YEARFRAC function
YEARFRAC returns the portion of a year between two dates as a decimal. It is useful for pro-rating, tenure and interest accrual, but different basis values give different answers.
Example
Read it as a sentence: find the fraction of a year from January 1, 2024 to July 1, 2024, which gives 0.5.
A
B
C
D
1
0.5
2
3
Syntax
YEARFRAC(start_date, end_date, [basis])
Arguments in [square brackets] are optional.
-
start_date(required) - Start date.
-
end_date(required) - End date.
-
basis(optional) - Day-count method: 0 US 30/360 (default), 1 actual/actual, 2 actual/360, 3 actual/365, 4 European 30/360.
Returns: The portion of a year between two dates, as a decimal.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=YEARFRAC(DATE(2026,1,1),DATE(2026,7,1)) | 0.5 | Basis 0 |
=YEARFRAC(DATE(2026,1,1),DATE(2026,7,1),1) | 0.495890411 | Basis 1 |
=YEARFRAC(DATE(2026,1,1),DATE(2026,7,1),2) | 0.502777778 | Basis 2 |
=YEARFRAC(DATE(2026,1,1),DATE(2026,7,1),3) | 0.495890411 | Basis 3 |
=YEARFRAC(DATE(2026,1,1),DATE(2026,7,1),4) | 0.5 | Basis 4 |
=YEARFRAC(DATE(2026,7,1),DATE(2026,1,1)) | 0.5 | Reversed |
=YEARFRAC(DATE(2026,1,1),DATE(2026,7,1),5) | #NUM! | Bad basis |
=YEARFRAC("x",1) | #VALUE! | Bad date |
Watch out for
- Different basis values give different answers; agree on one with your team.
- For exact years of age, DATEDIF with "Y" is cleaner.
Where it works
- Excel
- YEARFRAC is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Date and time functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .