Quick Navigation

YEARFRAC Function

Excel 2007+

Summary

YEARFRAC calculates the precise fraction of a year between two dates, essential for financial calculations like prorated interest, benefits accrual, or obligations based on actual time periods.

Syntax

YEARFRAC(start_date, end_date, [basis])

Parameters

Parameter	Type	Required	Description
start_date	`Date`	Yes	Required date marking the beginning of the period
end_date	`Date`	Yes	Required date marking the end of the period
basis	`Number`	No	Optional day count method: 0 (default US/NASD 30/360), 1 Actual/actual, 2 Actual/360, 3 Actual/365, 4 European 30/360

Using the YEARFRAC Function

YEARFRAC excels in financial modeling where precise time period fractions are needed. Use it to calculate prorated amounts for partial years in budgeting, loan amortization, employee benefits, or investment returns.

Common YEARFRAC Examples

Basic Year Fraction (Default Basis)

=YEARFRAC(DATE(2012,1,1), DATE(2012,7,30))

Calculates 0.58055556 using US (NASD) 30/360 basis between Jan 1 and Jul 30, 2012.

Leap Year with Actual/Actual

=YEARFRAC(DATE(2012,1,1), DATE(2012,7,30), 1)

Returns 0.57650273 using Actual/actual basis (366 days in 2012 leap year).

Fixed 365-Day Basis

=YEARFRAC(DATE(2012,1,1), DATE(2012,7,30), 3)

Returns 0.57808219 using Actual/365 basis regardless of leap year.

Frequently Asked Questions

Defaults to 0 (US/NASD 30/360), the most common financial convention.

US (NASD) 30/360 basis has special rules for February end dates that can affect calculations.

Always use DATE(yyyy,mm,dd) or cell references. Text dates cause #VALUE! errors.

Common Errors and Solutions

#VALUE!

Cause: start_date or end_date not recognized as valid dates

Solution: Use DATE function: DATE(2023,12,1) instead of text dates

#NUM!

Cause: basis parameter outside range 0-4

Solution: Use valid basis: 0,1,2,3, or 4

Incorrect February results

Cause: US 30/360 basis with Feb 28/29 start dates

Solution: Verify with Actual/actual (basis=1) or consult financial standards

Notes

Excel stores dates as serial numbers starting January 1, 1900 = 1
All arguments truncated to integers during calculation
Perfect for prorating annual amounts across partial periods
US/NASD 30/360 assumes 30-day months, 360-day year with adjustments

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365

Not available in: Excel 2003 and earlier

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+