How to Use YEARFRAC Function in Excel (7 Ways)

In Microsoft Excel, YEARFRAC Function is used to find the fraction of year represented by the real value of whole days between two dates. This function is useful for us to calculate our age also. In this article, we’ll get to learn how we can use this YEARFRAC function effectively in Excel with appropriate illustrations.

The above screenshot is an overview of the article, representing a few applications of the YEARFRAC function in Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

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 whole days between two dates

► Syntax

=YEARFRAC(start_date, end_date, [basis])

► Arguments Explanation

Here are different types of [basis] with their meaning:

7 Suitable Ways to Use YEARFRAC Function in Excel

1. Insert the YEARFRAC Function in Excel Without Basis

Let’s say, we have a dataset where Employee Names and their Joining Date along with their Resign Date and the Duration of their work with the company are given in Column B, Column C, Column D, and  Column E respectively.

Now, we calculate the working duration of the Employees by using the YEARFRAC Function without Basis. To calculate working time follow the steps that have been given below:

Step 1:

• First, select Cell E5 and go to Formula Bar and then type the formula in the Formula Bar.
• The formula will be:

Step 2:

• Now press the Enter button on your keyboard. After pressing the Enter button you will get the working duration of Jhon in the Company.

Step 3:

• After that, place your Cursor right corner bottom of Cell E5 and a Plus sign(+) pops up. Further press Left-Click on your Mouse and drag it downward. Finally, you get your desired result in Column E.

2. Apply the YEARFRAC Function With Basis

After using the YEARFRAC Function without Basis, now we apply the formula with Basis. To use the YEARFRAC Function with Basis, you now follow the steps which have given below:

Step 1:

• At first, select Cell F5.

• Now type the function in the Formula Bar and the function will be:

Step 2:

• Now press Enter button from your keyboard. After pressing Enter, the function will return 84722222.

• After that place, your Cursor right corner bottom of Cell F5, and a Plus sign(+) pops up. Now press Left-Click on your Mouse and drag it downward.

• After performing these steps, you will get a convenient result.

Though you will get your desired result, there are problems in Column F10 and Column F11. In these two cells, there is #NUM! Errors occurred. That’s happening when Basis < 0 or  Basis > 4.

3. Perform the YEARFRAC Function to Calculate Age

In this portion, we will know how to calculate the age of some persons by using the YEARFRAC Function. First, we will calculate the age of Jhon and then find the other person’s age that has been given in our table. Follow the steps:

Step 1:

• To find the age of Jhon, the related formula in Cell C5 will be:

• Now press You can get the age of Jhon to be 22.125.

Step 2:

• After getting Jhon’s age, you can easily get another person’s age. To calculate the age of other persons, place your cursor and drag it down while it shows the Plus(+) sign. Now we will get our desired output like our screenshot that has given below.

4. Use the YEARFRAC Function to Find Working Time in Companies

You can easily find the working time of the Employees of a Company by using the YEARFRAC Function. Let’s say, we will calculate the working time of Jhon from our dataset. To calculate the working time of Jhon, you have to follow the steps:

Step 1:

• At first, select Cell F5 and type the YEARFRAC Formula in that cell.
• The formula will be:
  
  =YEARFRAC(C5, D5, E5)
  

Step 2:

• Now press Enter and you get the working time of Jhon in Cell F5. The working time of Jhon is 847222222.

After calculating Jhon’s working time, keep your cursor on the Right-Bottom of the Cell F5 and press Left-Click on your Mouse and drag it downward. Now, look at Row 5 and Row 6, you can see the Joining Date and Resign Date of Jhon and Mary is the same but their working time is a little bit different. That’s happening because of the Basis.

5. Insert the INT Function and YEARFRAC Function in Excel

In Method 3, we have seen that how to calculate the Employee’s age in Real Numbers by using the YEARFRAC Function. As YEARFRAC Function can calculate the Employee’s age in Real Number, not in Integer. That’s why here we are now applying the combination of INT Function and YEARFRAC Function to calculate the Employee’s age in Integer. You will follow the steps:

Step 1:

• First, select Cell F5, and in this cell type the following formula. The formula will be:
  
  =INT(YEARFRAC(C5, D5))
  
• After typing the formula in the Formula Bar, press Enter and instantly you will get Jhon’s age in Integer The age of Jhon is 22.

Step 2:

• Finally, you will get the age of the Employees in Integer by using the combination formula of INT Function and YEARFRAC Function.

6. Calculate the Percentage of Year Complete by Using YEARFRAC Function

Now we can easily find how much time has passed in a certain year in terms of percentage by using the combination of the YEAR, DATE, and YEARFRAC Functions in Excel. Let,s say, we have used February 28, 2018, to calculate the time that has passed in this year from the beginning in terms of percentage. The formula that we have been used is given below:

So when February ends, we know that we are going with ⅙ th of the year, which means 0.16 or 16% of the year has passed.

We can also calculate the percentage of June 30, 2018, and September 30, 2018, that has been given in the screenshot.

7. Apply the IF Function and YEARFRAC Function in Excel

In the above Methods, we know about the YEARFRAC Function in detail. In this method, we are talking about the combination of YEARFRAC Function and IF Function. IF Function is a logical function. Let’s say, we have a dataset. We will take an Employee named Jhon who is permanent or not in terms of Duration of the working hours. To do that follow the steps:

Step 1:

• First, we select Cell F5 and type the formula. The formula will be:
  
  =IF(YEARFRAC(C5, D5)>5, “Permanent”, “Provisional”)
  
• Now press Enter. You will get your desired output. As Jhon is working for more than 5 years with that company, he is a Permanent Employee.

Step 2:

• In that way, you will get the result of others Employees.

Things to Remember

→ DATE() function is used when the dates that you are using are not working.

→ #NUM! error – Happens when

• Basis < 0
• Basis > 4.

→ #VALUE! error – Happens when:

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

Conclusion

I hope all of the suitable methods mentioned above to use the YEARFRAC function will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to comment if you have any questions or queries.