Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to calculate the years spent by each employees based on the Hire Date using the YEARFRAC function in Excel. Let’s get started
SAMPLE DATA
The screenshot below shows the HR dataset we wanna work with
YEARFRAC Function
The YEARFRAC function calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date).
Syntax
YEARFRAC(start_date, end_date, [basis])
In our case, we want to calculate how many years each employees have been working with us up to Today the 22/02/2023
To do that, in cell F2, execute the formula: =YEARFRAC(E2,TODAY())
Click Enter and Copy down the formula
Based on the above formula, the start year is the value that cell E2 wil return within the formula, while the end year is the TODAY() which will return todays date – 22/02/2023
From the screenshot below, we can see that the first employee has been working with us for 17 years and 6 months.
Suppose, we do not want to see the decimal without using the decrease decimal located in the Number group of the Home ribbon tab, we can then wrap the yearfrac formula with the INT Function which will return the integer only.
In cell cell G2, execute the formula: =INT(YEARFRAC(E2,TODAY()))
Click enter and Copy Down
From the screenshot below, we can see that the decimals are knock-off
See you in the next blog post