What it does
=YEARFRAC(A2,B2) returns the span between two dates as a fraction of a year. Multiply it by an annual amount to get the share for that period: principal × annual rate × YEARFRAC is the interest earned on a deposit.
The third argument picks the day-count basis. 0 or omitted: US 30/360 (every month 30 days). 1: actual days / actual year length. 2: actual days / 360. 3: actual days / 365. 4: European 30/360. Deposit interest is often calculated on actual/365, which is basis 3, but always check the basis your bank or contract uses.
If you need completed years, use DATEDIF; for a number of days, use DAYS. To round the decimal result, add ROUND, or ROUNDDOWN to round down.
Syntax
=YEARFRAC(start_date, end_date, [basis])=YILORAN(başlangıç; bitiş; [esas])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
start_date | Required | The date the period starts. |
end_date | Required | The date the period ends. Even if it is before the start date, the result is positive. |
basis | Optional | The day-count basis: 0 or omitted US 30/360, 1 actual/actual, 2 actual/360, 3 actual/365, 4 European 30/360. |
Examples
1Deposit interest on an actual/365 basis
Column F works out the time between the start and maturity dates in years with basis 3; column G multiplies the principal, the annual rate and that period to get the gross interest.
=YEARFRAC(D2,E2,3)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Principal | Annual Rate | Start | Maturity | Term (Years) | Gross Interest |
| 2 | DA-01 | 250,000 | 38% | 1-Sep-2026 | 1-Dec-2026 | 0.2493 | 23,684.93 |
| 3 | DA-02 | 100,000 | 36% | 15-Oct-2026 | 16-Nov-2026 | 0.0877 | 3,156.16 |
| 4 | DA-03 | 500,000 | 34% | 2-Jan-2026 | 2-Jul-2026 | 0.4959 | 84,301.37 |
- F2 (filled down to F4)
=YEARFRAC(D2,E2,3) - G2 (filled down to G4)
=B2*C2*F2
The period from the start of September to the start of December is 0.2493 years, and DA-01 earns 23,684.93 gross. For DA-02, roughly one month, the interest is 3,156.16.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2What three bases give for the same period
The same date pairs are calculated with basis 0 (30/360), basis 1 (actual/actual) and basis 3 (actual/365). The last row is from the leap year 2028.
=YEARFRAC(A2,B2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | End | Basis 0 (30/360) | Basis 1 (Actual/Actual) | Basis 3 (Actual/365) |
| 2 | 31-Jan-2026 | 31-Jul-2026 | 0.5000 | 0.4959 | 0.4959 |
| 3 | 1-Mar-2026 | 1-Sep-2026 | 0.5000 | 0.5041 | 0.5041 |
| 4 | 1-Jan-2028 | 1-Jul-2028 | 0.5000 | 0.4973 | 0.4986 |
- C2 (filled down to C4)
=YEARFRAC(A2,B2) - D2 (filled down to D4)
=YEARFRAC(A2,B2,1) - E2 (filled down to E4)
=YEARFRAC(A2,B2,3)
Because 30/360 counts every month as 30 days, six-month periods come out at exactly 0.5000; on an actual-day basis the same period is 0.5041. 2028 is a leap year, so basis 1 counts the year as 366 days (0.4973) and basis 3 as 365 (0.4986).
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: basis is a number outside 0-4.
Fix: Use 0, 1, 2, 3 or 4.
- Unexpected result
Cause: basis was left out, so the default 30/360 was used and the result differs slightly from an actual-day calculation.
Fix: For interest and proration, always state the basis, e.g. 3.
- #VALUE!
Cause: One of the dates is stored as text and cannot be read.
Fix: Convert the dates to real dates.
Good to know
- To round years of service to two decimals:
=ROUND(YEARFRAC(B2,C2,1),2). - To see the span in months with decimals, multiply by 12:
=YEARFRAC(A2,B2,1)*12.
In Nexell
Get Nexell to do it from a sentence
Add a column to the deposit list that works out the gross interest from the time between the start and maturity dates, counting actual days over 365
Convert to tableBring your deposit list into the tool and type this. Nexell works out the period and the interest with real Excel formulas and shows them in the preview; in the downloaded file, the interest changes when a rate or a date does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I get the number of years between two dates with decimals in Excel?
Use =YEARFRAC(A2,B2,1); it divides the actual number of days by the actual length of the year. If you only want whole years, use =DATEDIF(A2,B2,"y").