In Microsoft Excel, the YEARFRAC Function is used to find the fraction of the year represented by the real value of whole days between two dates. This function is useful for us to calculate our age. 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.
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) |
How to Use YEARFRAC Function in Excel: 7 Ways
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, go to Formula Bar, and then type the formula in the Formula Bar.
- The formula will be:
=YEARFRAC(C5, D5)
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, we apply the formula with Basis. To use the YEARFRAC Function with Basis, you now follow the steps which have given below:
Step 1:
- First, select Cell F5.
- Now type the function in the Formula Bar and the function will be:
=YEARFRAC(C5,D5,E5)Step 2:
- Now press the 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:
=YEARFRAC(C5, D5)
- 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 is 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 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 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 used is given below:
=YEARFRAC(DATE(YEAR(B5),1,1), B5)
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 will start with 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 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.
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 results of other 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
