YEARFRAC function
Quick answer
YEARFRAC returns the fraction of a year between two dates, such as 0.5 for six months.
- Syntax
=YEARFRAC(start_date, end_date, [basis])- Most common use
=YEARFRAC(B2,$B$5)returns 7.29 (Ana's years of service, 30/360 basis)
Use it for ages and tenure in decimal years, prorated salaries and fees, and accrued interest. The basis argument picks the day-count convention: 0, the default, is the US 30/360 method, and 1 uses actual days.
- Available in
- Every current version
- Category
- Date functions
- Returns
- A decimal number of years.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=YEARFRAC(start_date, end_date, [basis])
| Argument | Required? | What it is |
|---|---|---|
start_date | Required | The first date. |
end_date | Required | The second date. The order doesn't matter. |
basis | Optional | Optional: 0 (default) US 30/360, 1 actual/actual, 2 actual/360, 3 actual/365, 4 European 30/360. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | =YEARFRAC(B2,$B$5) | 7.29 | Ana's years of service, 30/360 basis |
| C3 | =YEARFRAC(B3,$B$5,1) | 2.83 | Raj's, using actual days |
| C4 | =INT(YEARFRAC(B2,$B$5,1)) | 7 | Whole years only: 7 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Employee | Start date | Years |
| Ana | 3/15/2019 | 7.29 |
| Raj | 9/1/2023 | 2.83 |
| 7 | ||
| As of | 6/30/2026 |
Practice YEARFRAC
Graded instantly, then re-checked on changed data.
Prorated bonus
D2, calculate the fraction of a year from the start date in B2 to the year end in F2, using actual days (basis 1). Fill down to D6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Ages are off by a little | The default basis 0 counts every month as 30 days. Use basis 1 for actual days, or DATEDIF for whole years. |
| #NUM! | basis isn't 0–4. |
| You wanted whole years | Wrap it in INT to drop the fraction: =INT(YEARFRAC(B2,TODAY(),1)). |
Tips
- For an age exact to the day, =DATEDIF(birthdate,TODAY(),"Y") gives whole years.
Questions
How do I calculate years between two dates in Excel?
=YEARFRAC(A2,B2,1) gives decimal years using actual days; wrap it in INT for whole years.
Which basis should I use?
1 (actual/actual) for ages and tenure. Finance agreements often specify 0 (30/360) or 3 (actual/365).
Updated Checked by running: every formula on this page was run by our formula engine on .