Excel YEARFRAC Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 105 of 105 · Full Excel course

The Excel YEARFRAC function returns the time between two dates as a decimal fraction of a year, for example 0.75 for nine months. An optional basis argument selects the day-count convention, from the US 30/360 default to actual calendar days. YEARFRAC is used for interest accruals, pro-rata charges, depreciation periods and age calculations where a fraction of a year matters.

Table of Contents

Syntax

=YEARFRAC(start_date, end_date, [basis])

Arguments

Argument	Required / Optional	Meaning
start_date	Required	The first date of the period, as a real date.
end_date	Required	The last date of the period. The order does not matter; the result is always positive.
basis	Optional	The day-count convention: 0 or omitted = US (NASD) 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360. Decimals are truncated; values outside 0 to 4 return #NUM!.

Basis options explained

Basis	Days counted	Year length	Typical use
0	30-day months, US rules for the 31st	360	US corporate bonds, many Excel defaults
1	Actual calendar days	365 or 366 (actual)	Ages, tenure, accurate elapsed time
2	Actual calendar days	360	Money-market instruments
3	Actual calendar days	365	Simple interest, some loans
4	30-day months, European rules for the 31st	360	Eurobonds, 30E/360 contracts

How YEARFRAC works

YEARFRAC counts the days between the dates under the chosen convention and divides by the year length for that convention. From 1 January 2026 to 1 June 2026, basis 0 gives 150/360 = 0.416667 and basis 1 gives 151/365 = 0.413699. Because the default basis 0 pretends every month has 30 days, it is slightly inaccurate for calendar purposes; use basis 1 whenever real elapsed time matters. Times are ignored, and text that is not a date returns #VALUE!.

Step-by-step example: pro-rata annual fee

Enter the membership start date in A2 (4 September 2026), the year-end in B2 (31 December 2026) and the annual fee in C2 (1,200).
In D2 enter =YEARFRAC(A2,B2,1). The result is 0.320548, the fraction of the year remaining (117 of 365 days).
In E2 enter =ROUND(C2*D2,2). The pro-rata fee is 384.66.
Change the basis to 0 and compare: the fraction becomes 0.325 and the fee 390.00, illustrating why the convention must match the contract.

Practical use cases

1. Age in complete years

=INT(YEARFRAC(BirthDate,TODAY(),1))

Basis 1 avoids the off-by-one errors that basis 0 can produce around birthdays.

2. Accrued interest on a deposit

=Principal*Rate*YEARFRAC(StartDate,EndDate,3)

3. Straight-line depreciation for a partial first year

=Cost/Life*YEARFRAC(PurchaseDate,DATE(YEAR(PurchaseDate),12,31),1)

4. Years of service with one decimal

=ROUND(YEARFRAC(HireDate,TODAY(),1),1)

5. Bond accrued interest under 30E/360

=Face*Coupon*YEARFRAC(LastCoupon,Settlement,4)

Common mistakes and tips

Relying on the default basis: basis 0 uses 30-day months. For calendar-accurate fractions and ages, specify basis 1.
Expecting a negative result: YEARFRAC returns the absolute value. Use IF(start>end,-1,1)*YEARFRAC(…) if the sign matters.
Whole years wanted: wrap in INT for complete years, or use DATEDIF with “y”.
Cell formatted as a date: the decimal will display as a date in 1900; set the format to General or Number.
#NUM! error: basis is outside 0 to 4, or a date is outside the Excel range.

YEARFRAC compared with DATEDIF, DAYS360 and subtraction

DATEDIF returns complete years, months or days as integers and is the clearest tool for ages and tenure expressed in whole units. YEARFRAC returns a continuous decimal, which is what interest, fee and depreciation formulas need, and its basis argument covers the day-count conventions that finance requires. DAYS360 returns the 30/360 day count itself rather than a fraction; YEARFRAC(a,b,0) equals DAYS360(a,b)/360 and YEARFRAC(a,b,4) equals DAYS360(a,b,TRUE)/360. Plain subtraction divided by 365 approximates basis 3 but ignores leap years. Match the basis to the wording of the contract or policy, state it in a labelled cell, and use basis 1 whenever the requirement is simply real elapsed time.

Related functions

DATEDIF: complete years, months or days between dates.
DAYS360: day count on a 30/360 basis.
DAYS: actual calendar days between dates.
TODAY: the current date as an end date.
See all lessons in the Excel Formulas course.

Frequently asked questions

What does the basis argument in YEARFRAC do?

It selects the day-count convention: 0 is US 30/360, 1 is actual/actual, 2 is actual/360, 3 is actual/365 and 4 is European 30/360. Use 1 for calendar-accurate results.