The YEARFRAC function in Google Sheets is useful if you want to get a decimal value that represents fractional years between two dates.
Meaning, the YEARFRAC function will return the number of days between two dates as a year fraction in decimal format.
Table of Contents
The rules for using the YEARFRAC function in Google Sheets are as follows:
- The date arguments can be passed as a text string enclosed with quotation marks or cell references that contain the dates.
- The YEARFRAC function will return the #VALUE! error if the start_date or end_date arguments are not valid dates or the third and optional argument is non-numeric.
- The YEARFRAC function will return the #NUM! error if the given basis argument is less than 0 or greater than 4.
Let’s take an example.
Camilla is an HR associate and is responsible for keeping employee records. One of her tasks is to identify each employee’s age, to date, based on the provided date of birth during their onboarding.
See her table below:
There are few ways that she can get each employee’s birthday.
However, Camilla used the easiest method she knows to get this information. She used the combination of the YEARFRAC function and INT function to get the age of their employees.
See her updated table below:
Pretty convenient, right?
Let’s have another example!
Camilla tried to identify each of their employee’s tenure, in years, based on their start date.
See what she’s able to yield based on the available information:
Using only the YEARFRAC function, Camilla was able to identify how long each of their employees has been working with them in terms of years.
Watch out for a more advanced tutorial and examples on how you can use the YEARFRAC function in the coming weeks. Be sure to subscribe to be notified.
Awesome! Let’s begin getting to know more about our YEARFRAC function in Google Sheets.
The Anatomy of the YEARFRAC Function
So the syntax (the way we write) of the YEARFRAC function is as follows:
=YEARFRAC(start_date, end_date, [basis])
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do computation or use a function.
- YEARFRAC() this is our YEARFRAC function. It returns the fraction of a year between two dates.
- start_date is the start of the period. The function includes the start_date in the calculation.
- end_date is the end of the period. The function includes the end_date in the calculation.
- [basis] is an optional argument. It specifies the type of day count basis to be used.
A Real Example of Using YEARFRAC Function
Let’s take a look at Camilla’s table below to see how the YEARFRAC function is used in Google Sheets.
The YEARFRAC function counts the number of days between two given dates and converts it to years.
In the first example above, the YEARFRAC function returned 5.37, which means that it’s been more than 5 years since Yaretzi was onboarded in the company.
But, what was the information provided to the YEARFRAC function to be able to yield this value?
Let’s take a closer look.
The YEARFRAC function needs three arguments to perform its job. These are the start_date, end_date, and the basis arguments.
In the example above, the first argument is the start date of the employee, which is ‘2/14/2016’.
For the second argument, Camilla used the TODAY function, instead of manually providing the date that day.
If you want to know more about the TODAY function, definition, and examples, feel free to visit this article.
For the third argument, she used ‘1’ which controls how days are counted when computing fractional years.
The default behavior, which is 0, is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The table below summarizes available options:
In this case, Camilla used ‘1’, which tells the YEARFRAC to count the actual days from the start date to today’s date.
That’s it! The YEARFRAC function calculated that the difference between ‘2/14/2016’ and ‘2/28/2021’, which is the date returned by the TODAY function, is 5.37 years.
You may make a copy of the spreadsheet using the link I have attached below.
How to Use YEARFRAC Function in Google Sheets
- Click on any cell to make it the active cell. For this guide, I will be selecting C2, where I want to show the result.
- Next, type the equal sign ‘=‘ to begin the function and then follow it with the name of the function, which is our ‘yearfrac‘ (or ‘YEARFRAC‘, not case sensitive like our other functions).
- Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.
- Now the exciting part! Let’s give our function its first argument, the start_date. You can pass a cell address that contains the start date or use another function that returns a date value. Alternatively, you can directly pass the date. Just don’t forget to enclose it with quotation marks (“”). In this example, I will use a cell reference. Type in ‘B2’.
- To let the Google sheet know that we’re done typing our first argument, we should now type in the delimiter or the character that separates each argument on a function. In this case, type comma ‘,’.
- Type in our second argument, which is the end_date. Similar to the first argument, you may use a cell address that contains the end date, you can directly pass the date to the YEARFRAC function, or use another function that returns a date value. In this case, we will use the TODAY function instead of manually typing in the exact date today. Type in ‘TODAY()’ and follow it with a comma (,).
- For the third argument, or the [basis], type ‘1’ which means we want the YEARFRAC function to count the actual dates between the first and second arguments.
- Finally, hit your Enter or Tab key. Cell C2 will now show you the return value of the YEARFRAC function, which is the fraction of years between the start and end date provided.
- Copy the formula down to the remaining rows.
That’s pretty much it. You can now use the YEARFRAC function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.