YEARFRAC In Excel

The YEARFRAC Excel function is a built-in Excel function used to get the year difference between two dates in fractions. For example, this function returns the difference between two dates in fractions like 1.5 years, 1.25 years, 1.75 years, etc. So, using this function, we can accurately find the year difference between two dates.

For example, suppose we have "Date 1" as 30-June-2020 in cell A2 and 19-Sep-2021 as "Date 2" in cell B2.

Then, the YEARFRAC function in cell C1 =(YEARFRAC A2,B2) will return the difference as  1.219 years as shown in the below image.

Likewise, we can use YEARFRAC function in Excel.

• start_date: What is the start_date or end_date of the available two dates?
• end_date: What is the end_date or greater date of the available two dates?
• : This is an optional argument. This argument needs to specify the day count method below available predetermined options.

By default, "0" is the basis. If we ignore the YEARFRAC function, we will take this into account. If you need any other options, you can mention them as mentioned in the above image.

YEARFRAC function in Excel helps us find the difference in years between two dates. Let us learn how to use this function with detailed functions.

Example #1 - Get Difference Between Two Dates

The YEARFRAC function is very useful when we want to know the difference between two dates in terms of years. For example, look at the two dates below.

We use the YEAR function to find the year difference between these dates. So, let us apply the same.

As we can see, we got the result as a full integer value, 1.

But the difference is more than one year, so we will use the YEARFRAC function in the C2 cell to get the full difference of a year.

The steps to use the YEARFRAC function are as follows:

1. Open the YEARFRAC function.

   
   

2. The start_date is the first argument, so select "Date 1" as the cell reference, A2 cell.

   
   

3. The second argument is end_date, so select the B2 cell as the reference.

   
   
   We need the default result, so leave it as it is.

4. Close the bracket and see the result.

   
   

Now, we have the difference between these two dates as 1.272 years.

Example #2 - Get Age Based On Date Of Birth

Now, we will find the age of persons based on their date of birth. Below is the data for this example.

From the above data, we need to find the age of each person as of today.

Let us open the YEARFRAC function.

The start_date will be DOB, so select B2 cell.

For end_date, insert the TODAY function in Excel because TODAY can return the current date per the system. Also, it is a volatile function that changes every day automatically, which makes the formula dynamic.

Close the bracket and see the result.

Now, drag the formula to cell C9 for the other values to be determined.

So now the age of each person in years, first-person “John” age is 31.378 years, “Peter” age is 20.647 years, and so on…

So far, so good with the YEARFRAC function, but if you tell the age in year fractions like 31.378, 20.647, and so on, people will understand accurately. So how about telling the age like this "30 years 4 months 15 days".

It makes a lot of sense.

However, to tell the age like this, we need to use different formulas, including the DATEDIF function. Therefore, we have applied the formula already below to find the difference, as said above.

So this will tell the exact age in terms of year, months, and days. Since we have applied the TODAY function as the end_date, it will change every day, and the result also will change automatically.

Example #3 - Using IF Condition

Now, we will see the YEARFRAC function as a supporting function for other functions. But, first, take a look at the below data.

It is the date of employees who work in an organization. This data has an employee's name and their respective joining date.

With this data, we need to find eligible persons for the bonus. To be eligible for the bonus, the person has to complete the 4.5 years tenure in the company. As we know, the YEARFRAC function can get the difference between two date infractions. Therefore, we will incorporate this with the IF condition.

Now, we have applied the logical test as if the YEARFRAC returns the year of service as >= 4.5 years, then we should get the result as "Eligible" or else "Not Eligible."

We get the following result.

Now, drag the formula to cell C9 for the other values to be determined.

Here, we have the result. Like this using the YEARFRAC function, we can make it in several ways.

• The argument should be within 0 to 4, anything more than 4 or anything less than 0 supplied, then we will get "#NUM! Error.
• The TODAY function is the volatile function used to get the current date per system.
• The start_date should be less than the end_date. Otherwise, we will get the error.