YEARFRAC Function in Excel Tutorial

Excel Tutorial

Introduction

The YEARFRAC function in Excel calculates the portion of a year between two dates. Unlike functions that return only complete years, YEARFRAC can return decimal values such as 1.5 or 2.25 years. It is useful for calculating employee service periods, investment durations, loan interest periods, and contract durations.

What Is the YEARFRAC Function in Excel?

The YEARFRAC function calculates the fraction of a year between a starting date and an ending date.

For example, suppose an employee joined a company on 01-Jan-2026 and the calculation date is 01-Jul-2026.

The employee has completed approximately half a year.

=YEARFRAC(A2,B2)

Expected Result: 0.50

The result is a decimal number representing the portion of a year.

You can use YEARFRAC to calculate:

  • Employee service duration in years.
  • Investment holding periods.
  • Loan and interest periods.
  • Contract duration.
  • Partial-year calculations.

YEARFRAC Function Syntax

=YEARFRAC(start_date,end_date,[basis])

Understanding the Arguments

ArgumentMeaning
start_dateThe starting date of the period.
end_dateThe ending date of the period.
basisOptional argument that specifies how Excel counts days in a year.

Understanding the Basis Argument

The basis argument controls how Excel calculates the fraction of a year.

BasisCalculation Method
0US (NASD) 30/360
1Actual/Actual
2Actual/360
3Actual/365
4European 30/360

If you omit the basis argument, Excel uses 0 by default.

Let’s understand these methods in simple language.

  • Basis 0 – US (NASD) 30/360: Treats a year as 360 days and a month as 30 days, following the US 30/360 convention.
  • Basis 1 – Actual/Actual: Uses the actual number of days and accounts for leap years.
  • Basis 2 – Actual/360: Uses the actual number of days but assumes a 360-day year.
  • Basis 3 – Actual/365: Uses the actual number of days but assumes a 365-day year.
  • Basis 4 – European 30/360: Uses a 360-day year and a 30-day month under the European convention.

Important: Different basis values can produce different results for the same dates. For financial calculations, select the basis required by the applicable agreement or financial convention.

Understanding YEARFRAC with an Example

Suppose:

  • Starting Date: 01-Jan-2026
  • Ending Date: 01-Jul-2026

Using Actual/Actual:

=YEARFRAC(A2,B2,1)

Expected Result: Approximately 0.50

This means the period represents approximately half a year.

Now consider a period of two complete years and six months.

The result would be approximately 2.50 years.

Remember:

YEARFRAC ResultMeaning
0.25Approximately three months
0.50Approximately six months
0.75Approximately nine months
1.00One year
1.50One and a half years
2.00Two years

These are approximate interpretations. The exact result depends on the dates and selected basis.

Practical Examples of YEARFRAC Function in Excel

Question 1: Calculate Employee Service Duration in Years

Problem Statement:

An HR department wants to calculate employee service duration in years, including partial years.

Employee Data:

Employee NameJoining DateCalculation Date
Rahul01-Jan-202501-Jul-2026
Priya01-Jan-202401-Jul-2026
Amit01-Jan-202301-Jul-2026
Neha01-Jul-202501-Jul-2026

Excel Formula:

Enter this formula in D2:

=YEARFRAC(B2,C2,1)

Expected Result:

Employee NameJoining DateCalculation DateService Duration
Rahul01-Jan-202501-Jul-20261.50
Priya01-Jan-202401-Jul-20262.50
Amit01-Jan-202301-Jul-20263.50
Neha01-Jul-202501-Jul-20261.00

Explanation:

The formula calculates the portion of a year between the joining date and calculation date.

The 1 tells Excel to use the Actual/Actual method.

For Rahul, the period from 1 January 2025 to 1 July 2026 represents one complete year and approximately six additional months. Therefore, the result is 1.50.

Concept Covered: Calculating employee experience with partial years.

Question 2: Calculate Investment Duration

Problem Statement:

An investor wants to calculate how long different investments have been held, expressed in years.

Investment Data:

Investment NamePurchase DateCalculation Date
Mutual Fund A01-Jan-202401-Jan-2026
Mutual Fund B01-Jul-202501-Jan-2026
Stock Investment01-Jan-202501-Jan-2026
Bond Investment01-Apr-202401-Jan-2026

Excel Formula:

Enter this formula in D2:

=YEARFRAC(B2,C2,1)

Expected Result:

Investment NamePurchase DateCalculation DateDuration in Years
Mutual Fund A01-Jan-202401-Jan-20262.00
Mutual Fund B01-Jul-202501-Jan-20260.50
Stock Investment01-Jan-202501-Jan-20261.00
Bond Investment01-Apr-202401-Jan-20261.75

Explanation:

The formula calculates the duration between the purchase date and calculation date.

The Actual/Actual basis uses the actual number of days in the period and accounts for leap years.

For Mutual Fund B, the period from 1 July 2025 to 1 January 2026 is approximately half a year.

Concept Covered: Measuring investment duration in fractional years.

Question 3: Compare Different Basis Methods

Problem Statement:

A finance team wants to understand how different basis values affect the calculated year fraction.

Date Data:

TransactionStart DateEnd Date
Transaction A01-Jan-202601-Jul-2026
Transaction B01-Feb-202601-Aug-2026
Transaction C01-Mar-202601-Sep-2026

Excel Formula:

Enter these formulas in D2 for the first transaction and copy them down.

Basis 0:

=YEARFRAC(B2,C2,0)

Basis 1:

=YEARFRAC(B2,C2,1)

Basis 2:

=YEARFRAC(B2,C2,2)

Basis 3:

=YEARFRAC(B2,C2,3)

Basis 4:

=YEARFRAC(B2,C2,4)

Expected Result:

TransactionBasis 0Basis 1Basis 2Basis 3Basis 4
Transaction A0.5000000.4958900.5027780.4986300.500000
Transaction B0.5000000.4958900.5027780.4986300.500000
Transaction C0.5000000.5041100.5027780.4986300.500000

Explanation:

All five formulas calculate the fraction of a year, but they use different day-count methods.

For example, Basis 2 divides the actual number of days by 360, while Basis 3 divides the actual number of days by 365.

The results differ because the methods use different assumptions about the length of a year and month.

Concept Covered: Understanding how the basis argument changes the result.

Question 4: Calculate Contract Duration

Problem Statement:

A company wants to calculate the duration of employee contracts in years, including partial years.

Contract Data:

Employee NameContract Start DateContract End Date
Rahul01-Jan-202530-Jun-2026
Priya01-Apr-202531-Mar-2026
Amit01-Jul-202431-Dec-2025
Neha01-Oct-202531-Mar-2026

Excel Formula:

Enter this formula in D2:

=YEARFRAC(B2,C2,1)

Expected Result:

Employee NameContract Start DateContract End DateDuration in Years
Rahul01-Jan-202530-Jun-20261.49
Priya01-Apr-202531-Mar-20261.00
Amit01-Jul-202431-Dec-20251.50
Neha01-Oct-202531-Mar-20260.50

Explanation:

The formula calculates the fraction of a year covered by each contract.

For Rahul, the period is just short of one year and six months. The Actual/Actual calculation returns approximately 1.49.

Concept Covered: Calculating contract periods with partial years.

Question 5: Calculate Loan Interest Period

Problem Statement:

A finance department wants to calculate the year fraction between loan disbursement and repayment dates using a 360-day year convention.

Loan Data:

Loan IDDisbursement DateRepayment Date
L10101-Jan-202601-Apr-2026
L10201-Feb-202601-May-2026
L10301-Mar-202601-Jun-2026
L10401-Apr-202601-Jul-2026

Excel Formula:

Enter this formula in D2:

=YEARFRAC(B2,C2,2)

Expected Result:

Loan IDDisbursement DateRepayment DateYear Fraction
L10101-Jan-202601-Apr-20260.250000
L10201-Feb-202601-May-20260.250000
L10301-Mar-202601-Jun-20260.255556
L10401-Apr-202601-Jul-20260.250000

Explanation:

The basis value 2 tells Excel to use Actual/360.

This means Excel counts the actual days between the two dates and divides them by 360.

For Loan L103, the period contains 92 actual days. Dividing 92 by 360 gives approximately 0.255556.

Concept Covered: Calculating a year fraction using the Actual/360 method.

Question 6: Calculate a Service Period Using Actual/365

Problem Statement:

A company wants to calculate employee service duration using an Actual/365 day-count method.

Employee Data:

Employee NameJoining DateCalculation Date
Aman01-Jan-202601-Apr-2026
Riya01-Feb-202601-May-2026
Karan01-Mar-202601-Jun-2026
Simran01-Apr-202601-Jul-2026

Excel Formula:

Enter this formula in D2:

=YEARFRAC(B2,C2,3)

Expected Result:

Employee NameJoining DateCalculation DateService Fraction
Aman01-Jan-202601-Apr-20260.246575
Riya01-Feb-202601-May-20260.246575
Karan01-Mar-202601-Jun-20260.252055
Simran01-Apr-202601-Jul-20260.249315

Explanation:

The basis value 3 tells Excel to use Actual/365.

Excel counts the actual days between the two dates and divides the result by 365.

For Karan, the period contains 92 days. Therefore, the result is approximately 92 divided by 365, or 0.252055.

Concept Covered: Calculating date differences using a fixed 365-day year.

Question 7: Calculate Duration Using the Default Basis

Problem Statement:

An HR department wants to calculate employee service duration using the default YEARFRAC method.

Employee Data:

Employee NameJoining DateCalculation Date
Rahul01-Jan-202601-Jul-2026
Priya01-Feb-202601-Aug-2026
Amit01-Mar-202601-Sep-2026
Neha01-Apr-202601-Oct-2026

Excel Formula:

Enter this formula in D2:

=YEARFRAC(B2,C2)

Expected Result:

Employee NameJoining DateCalculation DateService Duration
Rahul01-Jan-202601-Jul-20260.500000
Priya01-Feb-202601-Aug-20260.500000
Amit01-Mar-202601-Sep-20260.500000
Neha01-Apr-202601-Oct-20260.500000

Explanation:

The formula does not include the optional basis argument. Excel therefore uses its default Basis 0, the US (NASD) 30/360 method.

For these date ranges, the calculated fraction is 0.50.

Concept Covered: Using YEARFRAC without specifying the optional basis argument.

Student Practice Tasks

Try solving these tasks using only the YEARFRAC function.

Task 1: Calculate Employee Service Duration

EmployeeJoining DateCalculation Date
Mohit01-Jan-202501-Jul-2026
Sneha01-Apr-202501-Apr-2026
Varun01-Jul-202501-Jan-2026

Calculate the service duration in years using Basis 1.

Task 2: Compare Actual/360 and Actual/365

TransactionStart DateEnd Date
Transaction A01-Jan-202601-Apr-2026
Transaction B01-Feb-202601-May-2026
Transaction C01-Mar-202601-Jun-2026

Calculate the year fraction using Basis 2 and Basis 3.

Task 3: Calculate Contract Duration

ContractStart DateEnd Date
Contract A01-Jan-202401-Jan-2026
Contract B01-Jul-202501-Jan-2026
Contract C01-Apr-202501-Oct-2026

Calculate the contract duration in years using Basis 1.

Final Student Challenge: Build an Employee Service Duration Report

Problem Statement:

You are working in the HR department. Your manager wants a report showing employee service duration in decimal years.

Use the following employee data.

Employee Data:

Employee NameJoining DateCalculation Date
Rahul01-Jan-202401-Jul-2026
Priya01-Apr-202401-Jul-2026
Amit01-Jul-202501-Jul-2026
Neha01-Oct-202501-Jul-2026

Your Tasks:

  1. Calculate employee service duration using Basis 1.
  2. Calculate the same duration using Basis 0.
  3. Compare the results from both methods.
  4. Format the results to display six decimal places.
  5. Understand why the results may differ.

Expected Report Structure:

Employee NameJoining DateCalculation DateActual/ActualUS 30/360
Rahul01-Jan-202401-Jul-2026Your ResultYour Result
Priya01-Apr-202401-Jul-2026Your ResultYour Result
Amit01-Jul-202501-Jul-2026Your ResultYour Result
Neha01-Oct-202501-Jul-2026Your ResultYour Result

Complete the report using the YEARFRAC function with the correct basis values.

Key Takeaways

  • YEARFRAC calculates the fraction of a year between two dates.
  • Its syntax is =YEARFRAC(start_date,end_date,[basis]).
  • The result can be a decimal value such as 0.50 or 1.75.
  • The optional basis argument controls the day-count method.
  • Basis 0 uses the US (NASD) 30/360 method.
  • Basis 1 uses Actual/Actual.
  • Basis 2 uses Actual/360.
  • Basis 3 uses Actual/365.
  • Basis 4 uses European 30/360.
  • YEARFRAC is useful for employee service periods, contracts, investments, and financial calculations.

Frequently Asked Questions (FAQs)

1. What is the YEARFRAC function in Excel?

YEARFRAC calculates the fraction of a year between two dates and returns a decimal value.

2. What is the syntax of YEARFRAC?

The syntax is:

=YEARFRAC(start_date,end_date,[basis])

3. What does YEARFRAC return?

It returns a decimal number representing the portion of a year between two dates. For example, 0.50 represents approximately half a year.

4. What is the default basis in YEARFRAC?

The default basis is 0, which uses the US (NASD) 30/360 day-count method.

5. What is the difference between YEARFRAC Basis 1 and Basis 3?

Basis 1 uses actual days and accounts for leap years. Basis 3 uses actual days but always divides by 365.

6. Can YEARFRAC calculate employee experience?

Yes. It can calculate employee service duration in decimal years, including partial years.

7. Can YEARFRAC calculate loan interest periods?

Yes. It can calculate a year fraction using the day-count convention required for the financial calculation.

8. Why does YEARFRAC return a decimal number?

Because it calculates the complete and partial portions of a year rather than returning only whole years.

9. Can YEARFRAC calculate the difference between two dates in months?

YEARFRAC returns a fraction of a year, not a direct month count. A result such as 0.50 represents approximately six months.

10. Why do different basis values return different results?

Each basis uses a different method for counting days and defining the length of a year. Therefore, the same dates can produce different year fractions.