ExcelDateIntermediate

YEARFRAC

Returns the year fraction between two dates.

Read the syntaxReview worked examplesOpen the spreadsheet app
=YEARFRAC(start_date, end_date, [basis])
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Returns the year fraction between two dates.

The YEARFRAC function calculates the fractional portion of a year between two specific dates by determining the number of whole days and dividing by the number of days in a year. This is a vital calculation for financial analysis, particularly in bond interest accrual, pro-rating annual service fees, or determining accurate employee tenure. A key technical consideration is the [basis] argument, which dictates the day-count convention (e.g., US 30/360 vs. Actual/Actual). Best practices suggest using the DATE function or cell references for input parameters rather than hard-coded text strings, which can vary by regional settings. Note that if ate is later than ate, the function still returns a positive decimal value representing the interval length.

Quick reference

Syntax

=YEARFRAC(start_date, end_date, [basis])

Inputs

Arguments

start_dateStart date
Example: A2
end_dateEnd date
Example: B2
basis (optional)0=US 30/360, 1=Actual/Actual, etc.
Example: 1

Formula patterns

Examples

1

Calculate Employee Tenure

=YEARFRAC(A2, B2)
human resourcestenure
2

Financial Bond Interest (Actual/Actual)

=YEARFRAC(A3, B3, 1)
financeaccrued interest
3

Calculate Integer Age

=INT(YEARFRAC(A4, TODAY()))
personal dataage calculation

Avoid these issues

Common Errors

1

#VALUE!

Cause: One or both of the date arguments are text strings that Excel cannot recognize as valid dates.

Fix: Ensure the cells referenced contain valid serial dates or use the DATE(year, month, day) function to provide inputs.

2

#NUM!

Cause: The [basis] argument is a number less than 0 or greater than 4.

Fix: Correct the third argument to an integer between 0 and 4 (0=US 30/360, 1=Actual/Actual, 2=Actual/360, 3=Actual/365, 4=European 30/360).

Platform support

Compatibility

Excel 2007+Excel-first

Source: Microsoft Support

Common questions

Frequently Asked Questions

Returns the year fraction between two dates.

ate: Start date ate: End date basis: 0=US 30/360, 1=Actual/Actual, etc.

#VALUE!: Ensure the cells referenced contain valid serial dates or use the DATE(year, month, day) function to provide inputs. #NUM!: Correct the third argument to an integer between 0 and 4 (0=US 30/360, 1=Actual/Actual, 2=Actual/360, 3=Actual/365, 4=European 30/360).