Skip to content

Excel function · Date & time

Excel YEARFRAC function

YEARFRAC returns the time between two dates as a number of years with decimals: about 0.5 for six months. Use it for interest over a period, prorated payments and service in fractional years.

In Turkish Excel: YILORAN

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

English Excel=YEARFRAC(start_date, end_date, [basis])
Turkish Excel=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

ArgumentRequiredDescription
start_dateRequiredThe date the period starts.
end_dateRequiredThe date the period ends. Even if it is before the start date, the result is positive.
basisOptionalThe 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.

F2fx=YEARFRAC(D2,E2,3)
Deposit interest on an actual/365 basis: example table
ABCDEFG
1AccountPrincipalAnnual RateStartMaturityTerm (Years)Gross Interest
2DA-01250,00038%1-Sep-20261-Dec-20260.249323,684.93
3DA-02100,00036%15-Oct-202616-Nov-20260.08773,156.16
4DA-03500,00034%2-Jan-20262-Jul-20260.495984,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.

C2fx=YEARFRAC(A2,B2)
What three bases give for the same period: example table
ABCDE
1StartEndBasis 0 (30/360)Basis 1 (Actual/Actual)Basis 3 (Actual/365)
231-Jan-202631-Jul-20260.50000.49590.4959
31-Mar-20261-Sep-20260.50000.50410.5041
41-Jan-20281-Jul-20280.50000.49730.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

Type this in Nexell

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 table

Bring 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").

Related functions

All date & time functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free