How to Use the YEARFRAC Function in Excel – 7 Methods

This is an overview:

How to use YEARFRAC Function in Excel

 

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.

Insert the YEARFRAC Function in Excel Without Basis

Calculate the working time:

Step 1:

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

Insert the YEARFRAC Function in Excel Without Basis

Step 2:

  • Press Enter.

This is the output.

Insert the YEARFRAC Function in Excel Without Basis

Step 3:

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

Insert the YEARFRAC Function in Excel Without Basis


Method 2 – Applying the YEARFRAC Function With Basis

Step 1:

  • Select F5.

Apply the YEARFRAC Function With Basis

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

Step 2:

  • Press Enter. A

The function will return 84722222.

Apply the YEARFRAC Function With Basis

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

Apply the YEARFRAC Function With Basis

This is the output.

Apply the YEARFRAC Function With Basis

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.

Perform the YEARFRAC Function to Calculate Age

Step 2:

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

Perform the YEARFRAC Function to Calculate Age


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)

Use the YEARFRAC Function to Find Working Time in CompaniesStep 2:

  • Press Enter.

The output is 847222222.

 Find Working Time in Companies

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

 Find Working Time in Companies


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

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.

Insert the INT Function and YEARFRAC Function in ExcelStep 2:

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

Insert the INT Function


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

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)
Calculate the Percentage of Year 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:

Calculate the Percentage of Year


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

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.

Apply the IF Function

Step 2:

  • Get the results for the other Employees.

IF Function


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo