FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

# YEARFRAC In Excel

Updated on April 17, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

## What Is YEARFRAC Function 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.

### Key Takeaways

• The YEARFRAC Excel function is a built-in tool that accurately calculates the year difference between two dates in fractions, such as 1.5 years, 1.25 years, or 1.75 years, ensuring accurate year comparisons between the two dates.
• The [basis] argument should be between 0 and 4. If more than 4 or less than 0 is given, one may receive “#NUM! Error.”
• The TODAY function returns the current system date, and the starting date should be less than the end date to avoid errors.

### YEARFRAC() Excel Formula

• 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?
• [basis]: 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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

### How To Use YEARFRAC Excel Function?

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.

You can download this YEARFRAC Excel Template here – YEARFRAC Excel Template

### Examples

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

### Important Things To Note

• The [basis] 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.

### Frequently Asked Questions

1. What is the value of YEARFRAC in Excel?

The Excel YEARFRAC function calculates fractional years between two dates, with the option to specify day counting through the basis argument. It returns a decimal number.
For example, consider the below table.

Let us use YEARFRAC Function in Excel. Enter the formula, =YEARFRAC(B2,A2) in cell C2.

Press Enter key.

We can see the result as shown in the below image.

Likewise, we can use YEARFRAC function in Excel.

2. Does YEARFRAC calculate the number of years between two dates?

The YEARFRAC function calculates the fraction of a year between two dates, using a day-count convention to count the number of days between the dates and divide it by the basis.

3. What does the YEARFRAC function do in Google Sheets?

The YEARFRAC function in Google Sheets calculates the number of years between two dates, including fractional years, using a specific day count convention.

### Recommended Articles

This article has been a guide to YEARFRAC in Excel. Here, we discuss how to use the YEARFRAC Excel function to find the difference between two dates, get age based on the date of birth, and use it with IF Condition, examples, and a downloadable template. You may also look at these useful functions in Excel: –