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 

Different types of [basis]:

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:

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:

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:

• 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:

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.

Download Practice Workbook

Download the practice workbook.

<< Go Back to Excel Functions | Learn Excel