How to Use the YEARFRAC Function in Excel - 7 Methods

This is an overview:

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.

Calculate the working time:

Step 1:

Select E5, go to Formula Bar, and enter the formula:

=YEARFRAC(C5, D5)

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:

=YEARFRAC(C5,D5,E5)

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:

=YEARFRAC(C5, D5)

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

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

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)

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: