YEARFRAC

Calculate the fraction of a year between two dates using Excel's YEARFRAC function.

Date & Time
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=YEARFRAC(Start_date, End_date, [Basis]) Returns: Number

Arguments

Argument Required Description
Start_date Yes The initial date.
End_date Yes The ending date.
Basis No The day count basis to use. Defaults to 0 if omitted.

About

YEARFRAC returns a decimal number representing the fraction of a year between two dates. Use this when you need to calculate prorated amounts for salaries, interest, depreciation, or any other annual values that need adjustment based on partial years.

The function supports five different day count conventions through its optional basis parameter. The default (basis 0) uses the US 30/360 method, which assumes 30 days per month and 360 days per year. Choose basis 1 for actual/actual (the most precise method), basis 2 for actual/360 (common in money market calculations), basis 3 for actual/365, or basis 4 for European 30/360. Each convention affects how the fraction is calculated, so pick the one that matches your accounting or financial standards.

For related date calculations, try DATEDIF to find the difference between dates in various units, DAYS for simple day counts, or NETWORKDAYS to count working days only.

Examples

Basis comparison for interest calculations

See how different basis values produce different year fractions for the same dates. Try changing the start and end dates to compare how US 30/360, actual/actual, and actual/360 methods differ.

Spreadsheet editor

Dynamic tenure tracking with TODAY()

Combine YEARFRAC with TODAY() to calculate employee tenure that updates automatically. This example awards anniversary bonuses only to employees with 5+ years of service using IF.

Spreadsheet editor

Watch out for

⚠

Using text dates instead of proper date values

Typing dates as text strings like "1/15/2024" can fail or give wrong results depending on your regional settings.

→ Use the DATE function or cell references containing real dates. For example, DATE(2024,1,15) instead of "1/15/2024".

⚠

Wrong basis for your calculation type

Using the default basis (0) when your industry or accounting standards require a different day count convention leads to incorrect prorated amounts.

→ Check which basis your calculations require. Financial institutions often use basis 1 (actual/actual), while US corporate finance typically uses basis 0 (30/360).

⚠

February end-date errors with basis 0

When the start date is the last day of February and you use basis 0 (US 30/360), YEARFRAC may return an incorrect value.

→ Switch to basis 1 (actual/actual) if you're working with February end dates, or test your results carefully when February is involved.

Tips & notes

Always enter dates using the DATE function or cell references to avoid regional format issues. All arguments are truncated to integers, so decimal values in date serial numbers are ignored. If basis is outside the 0-4 range, YEARFRAC returns a #NUM! error.

Common questions

Which basis value should I use for YEARFRAC?

It depends on your accounting standards. Use basis 1 (actual/actual) for the most accurate calculation. Use basis 0 (US 30/360) for standard US financial calculations. Use basis 2 (actual/360) for money market instruments. Most corporate finance applications use either 0 or 1.

Can YEARFRAC handle dates in different years?

Yes, YEARFRAC works across any date range. If your dates span multiple years, the result will be greater than 1. For example, dates three years apart return approximately 3.0.

Why does YEARFRAC sometimes give unexpected results with February dates?

The US 30/360 basis (0) can produce incorrect results when the start date is the last day of February. If this affects your calculations, switch to basis 1 (actual/actual) for more reliable results with February dates.