YEARFRAC in Excel

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

Excel YEARFRAC 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.
Excel YEARFRAC 1

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

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

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 example, look at the below two dates.

Excel YEARFRAC Example 1

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

 Example 1.1

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

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

Steps to use YEARFRAC function are as follows:

  1. Open YEARFRAC function

    Excel YEARFRAC Example 1.2

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

    Excel YEARFRAC Example 1.3

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

    Excel YEARFRAC Example 1.5
    On the basis we need the default result, so leave it as it is.

  4. Close the bracket and see the result.

    Excel YEARFRAC Example 1.6

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.

Excel YEARFRAC Example 2

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

Let’s open the YEARFRAC function.

 Example 2.1

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

Excel YEARFRAC Example 2.2

For End Date, insert the TODAY function in excelInsert The TODAY Function In ExcelToday function is a date and time function that is used to find out the current system date and time in excel. This function does not take any arguments and auto-updates anytime the worksheet is reopened. This function just reflects the current system date, not the time.read more because TODAY can return the current date as per system, and also it is a volatile function that changes every day automatically, which makes the formula dynamic.

Example 2.3

Close the bracket and see the result.

Excel YEARFRAC Example 2.4

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

 Example 2.5

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

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

However, to tell the age like this, we need to use different formulas, i.e., DATEDIF functionDATEDIF FunctionDATEDIf is a date function that finds the difference between two dates, which can now be expressed in years, months, or days. This function's syntax is =DATEDIF (Start Date, End Date, Unit).read more. I have applied the formula already below to find the difference, as said above.

Excel YEARFRAC Example 2.6

So this will tell the accurate age in terms of year, months, and days. Since we have applied function TODAY 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 YEARFRAC function as a supporting function for other functions. Take a look at the below data.

Example 3.0

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 date infractions. We will incorporate this with the IF condition.

Excel YEARFRAC Example 3.1

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

Excel YEARFRAC Example 3.1.0

We get the following result.

 Example 3.2

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

Excel YEARFRAC Example 3.3

Here we have the result. Like this using the YEARFRAC function, we can make 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 a downloadable template. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>