## YEARFRAC in Excel

YEARFRAC Excel is a built-in Excel function that is used to get the year difference between two date infractions. This function returns the difference between two dates infractions like 1.5 Years, 1.25 Years, 1.75 Years, etc. So using this function we can find the year difference between two dates accurately.

**Syntax**

**Start Date:**What is the start date or least 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. With this argument, we need to specify the day count method with below available pre-determined options.

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

### Examples to use Excel YEARFRAC Function

#### Example #1 – Get Difference between Two Dates

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

To find the year difference between these two dates generally, we use the YEAR function. Let’s apply the same.

As we can see we got the result as full integer value i.e. 1.

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

**Step 1:** Open YEARFRAC function

**Step 2: **Start Date is the first argument, so select “Date 1” as the cell reference i.e. A2 cell.

**Step 3:** The second argument is **End Date **so select B2 cell as the reference.

The basis we need the default result, so leave as it is.

4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

**Step 4:** Close the bracket and see the result.

Ok, now we got the accurate difference between these two dates as 1.272 years.

#### Example #2 – Get Age Based on Date of Birth

Now we will see finding 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 on today.

Let’s open YEARFRAC function

**Start Date **will be DOB, so select B2 cell.

For **End Date **insert TODAY function in excel because since TODAY can return the current date as per system and also it is a volatile function which 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 got 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 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”.

This makes a lot of sense, isn’t it???

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

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

#### Example #3 – Using IF Condition

Now we will see YEARFRAC function as a supporting function for other functions. Take a look at the below data.

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

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

Now I 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 of it in several ways.

### Things to Remember

- [Basis] argument should be within 0 to 4, anything more than 4 or anything less than 0 supplied then we will get “#NUM! Error.
- TODAY is the volatile function to be used to get the current date as per system.
- The start date should be lesser than the end date otherwise we will get the error.

### Recommended Articles

This 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 using it with IF Condition along with examples and downloadable template. You may also look at these useful functions in excel –

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion