YEARFRAC in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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.

Syntax

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

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.

Examples to use Excel YEARFRAC Function

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

Example #1 – Get the 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.

Excel YEARFRAC Example 1

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

 Example 1.1

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.


    Excel YEARFRAC Example 1.2

  2. The start_date is the first argument, so select “Date 1” as the cell reference, 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
    We need the default result, so leave it as it is.

  4. Close the bracket and see the result.


    Excel YEARFRAC Example 1.6

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.

Excel YEARFRAC Example 2

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

Let us 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 per the system. 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 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 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. Therefore, we have applied the formula already below to find the difference, as said above.

Excel YEARFRAC Example 2.6

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.

Example 3.0

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.

Excel YEARFRAC Example 3.1

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

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

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

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: –