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
| Argument | Meaning |
|---|---|
| start_date | The starting date of the period. |
| end_date | The ending date of the period. |
| basis | Optional 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.
| Basis | Calculation Method |
|---|---|
| 0 | US (NASD) 30/360 |
| 1 | Actual/Actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 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 Result | Meaning |
|---|---|
| 0.25 | Approximately three months |
| 0.50 | Approximately six months |
| 0.75 | Approximately nine months |
| 1.00 | One year |
| 1.50 | One and a half years |
| 2.00 | Two 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 Name | Joining Date | Calculation Date |
|---|---|---|
| Rahul | 01-Jan-2025 | 01-Jul-2026 |
| Priya | 01-Jan-2024 | 01-Jul-2026 |
| Amit | 01-Jan-2023 | 01-Jul-2026 |
| Neha | 01-Jul-2025 | 01-Jul-2026 |
Excel Formula:
Enter this formula in D2:
=YEARFRAC(B2,C2,1)
Expected Result:
| Employee Name | Joining Date | Calculation Date | Service Duration |
|---|---|---|---|
| Rahul | 01-Jan-2025 | 01-Jul-2026 | 1.50 |
| Priya | 01-Jan-2024 | 01-Jul-2026 | 2.50 |
| Amit | 01-Jan-2023 | 01-Jul-2026 | 3.50 |
| Neha | 01-Jul-2025 | 01-Jul-2026 | 1.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 Name | Purchase Date | Calculation Date |
|---|---|---|
| Mutual Fund A | 01-Jan-2024 | 01-Jan-2026 |
| Mutual Fund B | 01-Jul-2025 | 01-Jan-2026 |
| Stock Investment | 01-Jan-2025 | 01-Jan-2026 |
| Bond Investment | 01-Apr-2024 | 01-Jan-2026 |
Excel Formula:
Enter this formula in D2:
=YEARFRAC(B2,C2,1)
Expected Result:
| Investment Name | Purchase Date | Calculation Date | Duration in Years |
|---|---|---|---|
| Mutual Fund A | 01-Jan-2024 | 01-Jan-2026 | 2.00 |
| Mutual Fund B | 01-Jul-2025 | 01-Jan-2026 | 0.50 |
| Stock Investment | 01-Jan-2025 | 01-Jan-2026 | 1.00 |
| Bond Investment | 01-Apr-2024 | 01-Jan-2026 | 1.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:
| Transaction | Start Date | End Date |
|---|---|---|
| Transaction A | 01-Jan-2026 | 01-Jul-2026 |
| Transaction B | 01-Feb-2026 | 01-Aug-2026 |
| Transaction C | 01-Mar-2026 | 01-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:
| Transaction | Basis 0 | Basis 1 | Basis 2 | Basis 3 | Basis 4 |
|---|---|---|---|---|---|
| Transaction A | 0.500000 | 0.495890 | 0.502778 | 0.498630 | 0.500000 |
| Transaction B | 0.500000 | 0.495890 | 0.502778 | 0.498630 | 0.500000 |
| Transaction C | 0.500000 | 0.504110 | 0.502778 | 0.498630 | 0.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 Name | Contract Start Date | Contract End Date |
|---|---|---|
| Rahul | 01-Jan-2025 | 30-Jun-2026 |
| Priya | 01-Apr-2025 | 31-Mar-2026 |
| Amit | 01-Jul-2024 | 31-Dec-2025 |
| Neha | 01-Oct-2025 | 31-Mar-2026 |
Excel Formula:
Enter this formula in D2:
=YEARFRAC(B2,C2,1)
Expected Result:
| Employee Name | Contract Start Date | Contract End Date | Duration in Years |
|---|---|---|---|
| Rahul | 01-Jan-2025 | 30-Jun-2026 | 1.49 |
| Priya | 01-Apr-2025 | 31-Mar-2026 | 1.00 |
| Amit | 01-Jul-2024 | 31-Dec-2025 | 1.50 |
| Neha | 01-Oct-2025 | 31-Mar-2026 | 0.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 ID | Disbursement Date | Repayment Date |
|---|---|---|
| L101 | 01-Jan-2026 | 01-Apr-2026 |
| L102 | 01-Feb-2026 | 01-May-2026 |
| L103 | 01-Mar-2026 | 01-Jun-2026 |
| L104 | 01-Apr-2026 | 01-Jul-2026 |
Excel Formula:
Enter this formula in D2:
=YEARFRAC(B2,C2,2)
Expected Result:
| Loan ID | Disbursement Date | Repayment Date | Year Fraction |
|---|---|---|---|
| L101 | 01-Jan-2026 | 01-Apr-2026 | 0.250000 |
| L102 | 01-Feb-2026 | 01-May-2026 | 0.250000 |
| L103 | 01-Mar-2026 | 01-Jun-2026 | 0.255556 |
| L104 | 01-Apr-2026 | 01-Jul-2026 | 0.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 Name | Joining Date | Calculation Date |
|---|---|---|
| Aman | 01-Jan-2026 | 01-Apr-2026 |
| Riya | 01-Feb-2026 | 01-May-2026 |
| Karan | 01-Mar-2026 | 01-Jun-2026 |
| Simran | 01-Apr-2026 | 01-Jul-2026 |
Excel Formula:
Enter this formula in D2:
=YEARFRAC(B2,C2,3)
Expected Result:
| Employee Name | Joining Date | Calculation Date | Service Fraction |
|---|---|---|---|
| Aman | 01-Jan-2026 | 01-Apr-2026 | 0.246575 |
| Riya | 01-Feb-2026 | 01-May-2026 | 0.246575 |
| Karan | 01-Mar-2026 | 01-Jun-2026 | 0.252055 |
| Simran | 01-Apr-2026 | 01-Jul-2026 | 0.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 Name | Joining Date | Calculation Date |
|---|---|---|
| Rahul | 01-Jan-2026 | 01-Jul-2026 |
| Priya | 01-Feb-2026 | 01-Aug-2026 |
| Amit | 01-Mar-2026 | 01-Sep-2026 |
| Neha | 01-Apr-2026 | 01-Oct-2026 |
Excel Formula:
Enter this formula in D2:
=YEARFRAC(B2,C2)
Expected Result:
| Employee Name | Joining Date | Calculation Date | Service Duration |
|---|---|---|---|
| Rahul | 01-Jan-2026 | 01-Jul-2026 | 0.500000 |
| Priya | 01-Feb-2026 | 01-Aug-2026 | 0.500000 |
| Amit | 01-Mar-2026 | 01-Sep-2026 | 0.500000 |
| Neha | 01-Apr-2026 | 01-Oct-2026 | 0.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
| Employee | Joining Date | Calculation Date |
|---|---|---|
| Mohit | 01-Jan-2025 | 01-Jul-2026 |
| Sneha | 01-Apr-2025 | 01-Apr-2026 |
| Varun | 01-Jul-2025 | 01-Jan-2026 |
Calculate the service duration in years using Basis 1.
Task 2: Compare Actual/360 and Actual/365
| Transaction | Start Date | End Date |
|---|---|---|
| Transaction A | 01-Jan-2026 | 01-Apr-2026 |
| Transaction B | 01-Feb-2026 | 01-May-2026 |
| Transaction C | 01-Mar-2026 | 01-Jun-2026 |
Calculate the year fraction using Basis 2 and Basis 3.
Task 3: Calculate Contract Duration
| Contract | Start Date | End Date |
|---|---|---|
| Contract A | 01-Jan-2024 | 01-Jan-2026 |
| Contract B | 01-Jul-2025 | 01-Jan-2026 |
| Contract C | 01-Apr-2025 | 01-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 Name | Joining Date | Calculation Date |
|---|---|---|
| Rahul | 01-Jan-2024 | 01-Jul-2026 |
| Priya | 01-Apr-2024 | 01-Jul-2026 |
| Amit | 01-Jul-2025 | 01-Jul-2026 |
| Neha | 01-Oct-2025 | 01-Jul-2026 |
Your Tasks:
- Calculate employee service duration using Basis 1.
- Calculate the same duration using Basis 0.
- Compare the results from both methods.
- Format the results to display six decimal places.
- Understand why the results may differ.
Expected Report Structure:
| Employee Name | Joining Date | Calculation Date | Actual/Actual | US 30/360 |
|---|---|---|---|---|
| Rahul | 01-Jan-2024 | 01-Jul-2026 | Your Result | Your Result |
| Priya | 01-Apr-2024 | 01-Jul-2026 | Your Result | Your Result |
| Amit | 01-Jul-2025 | 01-Jul-2026 | Your Result | Your Result |
| Neha | 01-Oct-2025 | 01-Jul-2026 | Your Result | Your 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.

