MS Excel: YEARFRAC function calculate fraction of a year between two dates

In real-world scenarios like loan interest calculations, employee benefits accrual, or age determination, simply knowing the number of full years between two dates isn't enough. That’s where Excel’s YEARFRAC function becomes essential—it calculates the exact fractional year between two dates.

This function helps bring precision into time-based financial models, HR systems, and reporting workflows that rely on accurate durations.

Syntax

=YEARFRAC(start_date, end_date, [basis])

Arguments:

Argument	Required	Description
start_date	✅ Yes	The beginning date of the period
end_date	✅ Yes	The ending date of the period
basis	❌ No	(Optional) The day count basis to use. Default is 0 (US 30/360)

Day Count Basis Options

Basis	Description	Calculation Method
0	US (NASD) 30/360	360-day year, 30-day months
1	Actual/Actual	Real calendar days
2	Actual/360	Actual days / 360
3	Actual/365	Actual days / 365
4	European 30/360	30-day months, 360-day year (EU style)

Examples

Example 1: Basic Calculation

=YEARFRAC("2024-01-01", "2025-01-01")

Result: 1

Example 2: Half-Year Period

=YEARFRAC("2024-01-01", "2024-07-01")

Result: 0.5

Example 3: With a Specific Basis

=YEARFRAC("2024-01-01", "2025-01-01", 3)

Result: 1(Based on actual days / 365)

Example 4: Age Calculation

Assume A2 contains a date of birth:

=YEARFRAC(A2, TODAY())

Result: Age in decimal form (e.g., 25.73)

Sample Table

Start Date	End Date	Formula	Result
2024-01-01	2024-07-01	=YEARFRAC(A2, B2)	0.5
2023-05-15	2025-05-15	=YEARFRAC(A3, B3, 1)	2.00
2020-03-01	2021-02-28	=YEARFRAC(A4, B4, 0)	0.983

Common Errors & Fixes

Error	Reason	Fix
#VALUE!	One or both dates are invalid	Use DATE() function or valid date cells
Incorrect result	Wrong basis selection	Check documentation for correct basis

Related Functions

Function	Description
DATEDIF()	Returns difference between dates in years, months, or days
YEAR()	Returns the year part of a date
TODAY()	Returns the current system date
DATE()	Creates a date from year, month, day

Summary Table

Feature	Details
Function Name	YEARFRAC
Purpose	Calculate fractional years
Returns	Decimal value (e.g., 1.25)
Inputs	Two dates, optional basis
Useful For	Financial models, age calc, reporting
Available In	Excel 2007 and later

Conclusion

The YEARFRAC function is a versatile and precise tool that brings accuracy to year-based calculations in Excel. Whether you're managing loans, benefits, or historical timelines, using YEARFRAC ensures your outputs reflect actual time elapsed.