YEARFRAC Function in Excel: Fraction of a Year Between Dates

📎 This article includes 1 downloadable practice file ↓

Date & TimeLevel: IntermediateAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. YEARFRAC in practice
6. Where you will use it
7. Worked example
8. Mistakes people make
9. Related functions
10. Continue learning

YEARFRAC returns the fraction of a year between two dates — for pro-rata interest, depreciation and exact age.

Syntax

=YEARFRAC(start_date, end_date, [basis])

Argument	What it means
basis	0 US 30/360 (default), 1 actual/actual, 3 actual/365.

Examples

Example 1

=YEARFRAC(A2, B2, 1)

Exact fraction of a year.

Example 2

=C2*D2*YEARFRAC(A2, B2, 3)

Simple interest for a partial year on an actual/365 basis.

YEARFRAC in practice

Where you will use it

• Age in decimal years
• Interest for part of a year
• Service years for gratuity

Worked example

Start	End	Formula	Result
01-Apr-2026	01-Oct-2026	=ROUND(YEARFRAC(A2,B2,1),3)	0.501
15-Aug-1990	04-Oct-2026	=ROUND(YEARFRAC(A3,B3,1),3)	36.138
01-Jan-2026	31-Dec-2026	=ROUND(YEARFRAC(A4,B4,1),3)	0.997

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Basis choice	Basis 1 uses actual days; default 0 is 30/360
Exact completed years	Use DATEDIF with "y"

Related functions

DATEDIF · DAYS

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Date & Time guides →

TIMEVALUE Function in Excel: Text Time to Real TimeDateLevel: IntermediateAvailable in: Excel 2007+TIMEVALUE turns text like "2:45 PM" into a real time…DATEVALUE Function in Excel: Convert Text Dates Into Real DatesDATEVALUE turns a date written as text into a real date number. It follows…MINUTE Function in Excel: Minute From a TimeDateLevel: BeginnerAvailable in: Excel 2007+MINUTE returns 0–59 from a time.Syntax=MINUTE(serial_number)ArgumentWhat it.SECOND Function in Excel: Second From a TimeDateLevel: BeginnerAvailable in: Excel 2007+SECOND returns 0–59. Mostly used to strip seconds from machine.

📎 Practice files for this article

• 📗
  Practice workbook: Dates & times22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 34 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.