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.

How to use YEARFRAC Function in Excel

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

Argument Required/Optional Explanation
start_date Required The date from where the calculation begins
end_date Required The date from where the calculation ends
basis Optional While calculating the fractional year basis is needed

The type of day count basis to use

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

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)

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.

Insert the YEARFRAC Function in Excel Without Basis

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:
=YEARFRAC(C5, D5)

Insert the YEARFRAC Function in Excel Without Basis

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.

Insert the YEARFRAC Function in Excel Without Basis

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.

Insert the YEARFRAC Function in Excel Without Basis


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.

Apply the YEARFRAC Function With Basis

  • Now type the function in the Formula Bar and the function will be:
=YEARFRAC(C5,D5,E5)

Step 2:

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

Apply the YEARFRAC Function With Basis

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

Apply the YEARFRAC Function With Basis

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

Apply the YEARFRAC Function With Basis

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:

=YEARFRAC(C5, D5)

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

Perform the YEARFRAC Function to Calculate Age

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.

Perform the YEARFRAC Function to Calculate Age


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)

Use the YEARFRAC Function to Find Working Time in Companies

Step 2:

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

 Find Working Time in Companies

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.

 Find Working Time in Companies


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.

Insert the INT Function and YEARFRAC Function in Excel

Step 2:

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

Insert the INT 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:

=YEARFRAC(DATE(YEAR(B5),1,1), B5)
Calculate the Percentage of Year

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.

Formula Breakdown:

We will start from the YEAR Function. The YEAR function refers to the year of a date. The function will be: =YEAR(B5) The DATE function needs three arguments. The first argument is a particular year, the second argument is a month and the third argument is a day. As We select Cell B5 and the formula becomes: =DATE(YEAR(B5), 1,1) Here, YEAR(B5) refers to the year 2018, 1 represents the January month, and the other 1 represents the first day of the year. After that, the final formula will be: =YEARFRAC(DATE(YEAR(B5), 1, 1), B5)

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

Calculate the Percentage of Year


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.

Apply the IF Function

Step 2:

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

IF Function


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.

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo