If you’re working with dates in Google Sheets and need to calculate the exact number of years between two dates, the YEARFRAC function is here to save the day. In this blog post, we’ll dive into how to use the YEARFRAC function in Google Sheets to calculate the number of years (including fractions of years) between two dates.
We’ll take you through a practical example, explain the formula step by step, and show you the magic of how simple and efficient this function can be!
What is the YEARFRAC Function?
The YEARFRAC function calculates the difference between two dates in terms of years, giving you the fractional part of the year as well. This is especially useful when you want to determine the time period between two dates in terms of a decimal (e.g., 1.5 years).
YEARFRAC(start_ date, end_ date, [basis])
- start_ date: The date from which you want to start counting.
- end_ date: The date until which you want to count.
- basis: An optional argument that specifies how the year is calculated. In most cases, you can leave this out.
Now, let’s move on to a real-world example!
Example Data
Here is the dataset we’ll be working with. We have several employees, their start dates, and end dates. Using the YEARFRAC function, we want to calculate the exact number of years they’ve worked between their start and end dates.
The Formula: How It Works
We’ll use the following formula to calculate the number of years between each employee’s start date and end date:
=YEARFRAC(B4, C4)
Here’s what the formula does:
YEARFRAC(B4, C4): The function takes the start date from cell B4 and the end date from cell C4 and calculates the number of years between them. The result is a decimal number that includes any fractions of a year (for example, 1.5 means 1 year and 6 months).
By dragging this formula down the column, you can apply it to the entire dataset, calculating the number of years for all employees.
Explanation of the Formula:
Let’s break it down further:
- B4: This refers to the start date (e.g., “16-Jul-2022”).
- C4: This refers to the end date (e.g., “1-Jan-2024”).
The formula calculates the total number of years between these two dates, accounting for any partial years.
The result will give you the exact number of years worked, which might be a full number (like 2) or include a fraction (like 1.75 years).
Formula Output
Here’s the result after applying the formula:
The YEARFRAC function gives you the years worked in decimal form, meaning you can now easily identify employees who have worked for over 1 year or even those nearing their 2-year mark.
Why Use YEARFRAC?
You may wonder why not just count the number of years manually? Here are a few benefits of using the YEARFRAC function:
- Precision: It gives you the fractional part of the year, making it accurate for calculations involving months or even days.
- Automation: The formula adjusts automatically when dates change, saving you the hassle of manual calculations.
- Time-Saving: Whether you’re handling a small or large dataset, this function quickly delivers the results you need without having to perform tedious date conversions.
Final Thoughts
The YEARFRAC function in Google Sheets is a powerful tool for anyone working with date ranges. Whether you’re calculating employee tenures, project timelines, or anything involving periods of time, this function gives you a precise way to measure years, even including fractions.
Visit our YouTube channel to learn step-by-step video tutorials
Youtube.com/@NeotechNavigators