# How to Use the YEARFRAC Function in Excel – 7 Methods

This is an overview:

## Introduction to the YEARFRAC Function in Excel

Function Objective

The YEARFRAC function is used to calculate the fraction of the year represented by the number of the whole days between two dates.

Syntax

=YEARFRAC(start_date, end_date, [basis])

Arguments

Argument Required/Optional Explanation
start_date Required The date from which the calculation begins
end_date Required The date in which the calculation ends
basis Optional While calculating the fractional year,  the type of day count basis is needed

Different types of [basis]:

Basis Description
0 or omitted 30 day months/360 day years(U.S. – NSDA)
1 Actual days in the months/Actual days in the years
2
Actual days / 360 days per year
3
Actual days / 365 days per year
4
30 days per month / 360 days per year(European)

### Method 1 – Use the YEARFRAC Function in Excel Without Basis

The dataset showcases Employee Names, their Joining Date, their Resign Date and the Duration of their work.

Calculate the working time:

Step 1:

• Select E5, go to Formula Bar, and enter the formula:
`=YEARFRAC(C5, D5)`

Step 2:

• Press Enter.

This is the output.

Step 3:

• Drag down the Fill Handle to see the result in the rest of the cells.

### Method 2 – Applying the YEARFRAC Function With Basis

Step 1:

• Select F5.

• Enter the function in the Formula Bar:
`=YEARFRAC(C5,D5,E5)`

Step 2:

• Press Enter. A

The function will return 84722222.

• Drag down the Fill Handle to see the result in the rest of the cells.

• This is the output.

In F10 and F11, the #NUM! Error is displayed: the Basis < 0 or  Basis > 4.

### Method 3 – Perform the YEARFRAC Function to Calculate Age

Step 1:

• To find the John’s age, enter the formula in C5:

`=YEARFRAC(C5, D5)`

• Press Enter.

The output is 22.125.

Step 2:

• Drag down the Fill Handle to see the result in the rest of the cells.

### Method 4 – Using the YEARFRAC Function to Find Working Time in Companies

To calculate John’s working time:

Step 1:

• Select F5 and enter the YEARFRAC Formula:
`=YEARFRAC(C5, D5, E5)`

Step 2:

• Press Enter.

The output is 847222222.

Drag down the Fill Handle to see the result in the rest of the cells.

In Row 5 and Row 6, you can see the Joining Date and Resign Date of John and Mary is the same, but their working time is different, due to the Basis.

### Method 5 – Use the INT Function and the YEARFRAC Functions in Excel

Combine the INT Function and YEARFRAC Function to calculate the Employee’s age in Integers.

Step 1:

• Select F5 and enter the following formula:
`=INT(YEARFRAC(C5, D5))`
• Press Enter.

John’s age is 22.

Step 2:

• Drag down the Fill Handle to see the result in the rest of the cells.

### Method 6 – Calculate the Percentage of the Year Using the YEARFRAC Function

Find how much time has passed in a year in percentage.

February 28, 2018 was used as starting date to calculate the time passed in this year.

The formula is:

`=YEARFRAC(DATE(YEAR(B5),1,1), B5)`
When February ends,  0.16 or 16% of the year has passed.

Formula Breakdown:

The YEAR function refers to the year of a date: =YEAR(B5) The DATE function needs three arguments: the year, the s month and the day. As B5 was selected, the formula becomes: =DATE(YEAR(B5), 1,1) Here, YEAR(B5) refers to 2018, 1 represents January, and the other 1 represents the first day of the year. The final formula will be: =YEARFRAC(DATE(YEAR(B5), 1, 1), B5)

You can also calculate the percentage of June 30, 2018, and September 30, 2018:

### Method 7 – Applying the IF Function and the YEARFRAC Function in Excel

Combine the YEARFRAC Function and IF Function.

Check John’s working hours.

• Select F5 and enter the formula.
`=IF(YEARFRAC(C5, D5)>5, “Permanent”, “Provisional”)`
• Press Enter.

John is a Permanent Employee, with more than 5 years in the company.

Step 2:

• Get the results for the other Employees.

## Things to Remember

#NUM! error

• Basis < 0
• Basis > 4.

#VALUE! error

• Arguments are not a valid Date
• The Basis is non-numeric.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF