YEARFRAC in Google Sheets

What Is YEARFRAC in Google Sheets?

The YEARFRAC in Google Sheets calculates the fraction of a year between two given dates. This is based on a specified day count convention. It returns the number of years and fractional years between the specified two dates. The mandatory arguments, “startdate” and “enddate,”  can be either valid dates enclosed in double quotes or cell references containing dates. We use YEARFRAC in financial calculations for calculating interest rates and investment periods. YEARFRAC also takes into account leap years and uses a specified count convention to estimate the fraction of a year.

In the example below, we have two dates in the same month of two different years. Let us use YEARFRAC in Google Sheets to calculate the years between them.

Enter the following formula in cell A3. =YEARFRAC(A1,A2). You can observe the output to be exactly 1 without any decimal as they belong to the same day and month of the succeeding year. Let us change the month to August and observe the difference where we get the fractional value.

YEARFRAC-in-Google-Sheets-Definition
Key Takeaways
  1. The YEARFRAC function takes two dates and returns the years between them, including fractions of a year. You can optionally specify different methods of counting days.
  2. The syntax of YEARFRAC in Google Sheets is as follows: =YEARFRAC(start_date, end_date,[day_count_convention])
    • start_date – The date for the calculation to start. end_date – The date for the calculation to end.
  3. The return value of this function is a decimal number representing the fraction of a year between the start and end dates.
  4. The YEARFRAC function can be combined with other functions, such as the IF function, to create more complex calculations.

Syntax

The YEARFRAC function in Google Sheets has the following syntax.

=YEARFRAC(start_date,end_date,[day_count_convention])

  1. start_date – The date from which we calculate
  2. end_date – The date where the calculation ends.
  3. day_count_convention – Optional day count method.
  4. 0 represents US (NASD) 30/360
  5. 1 represents the actual number of days.
  6. 2 represents the actual number of days but with a 360-day year.
  7. 3 represents the number of days but assumes a 365-day year. This option doesn’t recognize leap year.
  8. 4 represents European 30/360. It is similar to 0 but uses European standards instead of US.

How to Use YearFrac function in Google Sheets?

Using YEARFRAC in Google Sheets is very simple. Here, we walk you through a step-by-step process for applying this function.

  1. Manually enter the formula
  2. Through the Google Menu Bar

Manually enter the formula

Step 1: First, open Google Sheets. Then, in a new spreadsheet or an existing one, enter the dates for which you want to use the YEARFRAC function.

Enter the start date and the end date. Ensure that the dates are in a format recognized by Google Sheets, such as mm/dd/yyyy.

How-to-Use-YearFrac-function-in-Google-Sheets-Step-1

Step 2: Choose the cell where you want the result to appear. Here, we choose C2.

Type the YEARFRAC formula below.

=YEARFRAC(A2, B2)

How-to-Use-YearFrac-function-in-Google-Sheets-Step-2

Step 3: Press Enter to get the result. Now, drag the formula to C3 to get the result for the next set of dates.

How-to-Use-YearFrac-function-in-Google-Sheets-Step-3

Thus, we have used the YEARFRAC function in Google Sheets manually.

Using the Google Menu bar

  1. Choose the cell where you want to enter the formula. Now, go to the menu bar and click on “Insert” – “Function” – “Date” – “YEARFRAC.”
Using-the-Google-Menubar-1

YEARFRAC is very versatile in financial analysis. It can be used to calculate the duration of investments, determine the age of accounts receivable in finance, and analyze employee tenure. These are some of the scenarios we would explore in the examples below.

Let us look at some of the examples where we put this helpful function in Google Sheets.

Examples

Example #1 – Calculate Investment Period

YEARFRAC in Google Sheets is used in many financial calculations, such as calculating the investment period. Let us assume we made an investment on January 1st, 2015. It matures on December 31st, 2021.

Step 1: Enter the two dates in the Google sheet. To calculate the duration of the investment, we will use the formula =YEARFRAC(A1, B1), as shown below.

Example-1-Step-1

Step 2: Press Enter. This will give you the total number of years between the two dates(including fractions). If you have specific day count conventions to follow, you can adjust the basis parameter.

Example #2 – Calculate Employee Tenure

Another important use of the YEARFRAC function is to calculate the tenure of an employee or find someone’s age to the present date. Here, we combine it with the TODAY function to calculate the employee tenure.

When combined with the TODAY function, we can calculate age.

Calculating Employee Tenure.

Step 1: For this, we first enter the employee’s date of joining. We then must find his current tenure, for which we use the TODAY function.

Example-2-Step-1

Step 2: Now, apply the YEARFRAC function in cell A3 as follows:

=YEARFRAC(A1,A2,1)

Here, 1 means the actual days of the year.

Example-2-Step-2

Step 3: Press Enter. You get the employee’s tenure in the organization to a fraction.

The result will be updated for every day you open the spreadsheet as TODAY() is a dynamic function.

Example-2-Step-3

Many company benefits, such as shares, bonuses, and vacation days, are decided based on tenure. You can use YEARFRAC to determine if an employee meets the eligibility criteria.

Example #3 – YEARFRAC with Date function

Let us look at how to use the YEARFRAC in Google Sheets.

Step 1: We can use the DATE function to convert a value to the DATE format. We can use the function with YEARFRAC using the following function.

=INT(YEARFRAC(DATE(2023,12,6),DATE(2022,8,9),1))

Example-3-Step-1

Step 2: Press Enter. You will get the fractional value between the dates. We use the INT function to round off the value.

Example-3-Step-2

As indicated, the YEARFRAC function can be used in various scenarios to find the fractional value between two dates. You can even convert regular text to dates using the DATE function. It is especially useful in scenarios where clients allocate you a project, and you must complete them within specified deadlines.

Example #4 – YEARFRAC with Today function

Calculating Age in Whole Years with TODAY() function

Let’s add to the other examples by converting the fractional result of the formula to an integer. For this, we can use INT function.

Step 1: To calculate the age, first enter the person’s date of birth in cell A1. Then, enter TODAY() in cell A2 to get today’s date.

Example-4-Step-1

Step 2: Now, to calculate the person’s age and then round it down to a whole number, use the following function in cell A3.

=INT(YEARFRAC(A1,A2,1))

Example-4-Step-2

Step 3: Adding the INT function is used to provide age as a whole number.

Example-4-Step-3

Now, you get the age of the person as shown above in an integer form.

Important Things To Note

  1. Always ensure that you give the correct ‘basis’ parameter based on your scenario to avoid untoward results when using YEARFRAC.
  2. If the start_date or end_date arguments are not valid Excel dates, the function will give a #VALUE! error.
  3. YEARFRAC is used with other date functions like DATEDIF. DATEDIF gives whole years, months, or dates, but YEARFRAC gives a fractional year output.
  4. YEARFRAC is used in financial modeling, investment analysis, calculation of employee tenure, loan calculations, etc.

Frequently Asked Questions (FAQs)

1. What are the options available in the basis parameter in the YEARFRAC function in Google Sheets?

The basis parameter represents the following options.
0 (or omitted): US (NASD) 30/360 – The year is divided into 12 months of 30 days each.
1: Actual/actual: The exact number of days in each month and the year (365 or 366 for leap years).
2: Actual/360: Calculations are based on a 360-day year.
3: Actual/365: Interest calculations are based on a 365-day year
4: European 30/360: In European markets, the year is treated as 360 days, while the months are 30 days each.
The calculation between the start and end dates is counted based on the number chosen as the basis.’

2. How are leap years handled with YEARFRAC and what happens if the start date is after the end date?

The basis argument should be set to 1 to handle leap years accurately in your calculations. If the end date is earlier, you get a negative number, which indicates that the year’s fraction is in reverse.

3. What are some of the errors you get when you use YEARFRAC in Google Sheets?

Since it involves calculations using dates, the inputs to the YEARFRAC Google Sheets function should be given carefully. Otherwise, you may get the following errors.
#NUM – You get this error when the input is a number and not a valid date.
#VALUE! – Ensure that both the date arguments are valid date values. If these arguments are non-numeric, you get this error.

Download Template

This article must help understand YEARFRAC in Google Sheets with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is YEARFRAC in Google Sheets. We learn how to insert YEARFRAC in Google Sheets, remove, reset, examples, working template. You can learn more from the following articles.

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *