This guide will explain how to calculate years of service in Excel using four simple and efficient methods.
Excel is a popular tool and choice for numbers and calculations. Furthermore, performing calculations in Excel is very easy and efficient because of its in-built functions.
Because of its several features and tools, there are many things you can perform and calculate in Excel. And one of these is the ability to calculate the years of service of an individual or employee.
And we can calculate the years of service in Excel using its many available functions. For instance, we can utilize the INT function, the YEARFRAC function, the DATEDIF function, the DAYS360 function, and the DATE function.
Let’s take a sample scenario.
Suppose you were tasked to create a directory of past employees containing information such as their name, start date and end date of work, and their years of service.
But, it would take too long for you to calculate each employee’s years of service individually. So you opted to use a function to finish your task efficiently.
And worry not! There are several methods we can use to calculate years of service in Excel. Before we start learning these methods, let’s first understand the syntax of some of these functions.
The Anatomy of the YEARFRAC Function
The syntax or the way we write the YEARFRAC function is as follows:
=YEARFRAC(start_date,end_date,[basis])
Let’s take apart this formula and understand what each term means:
- = this is how we start any function in Excel.
- YEARFRAC() is our
YEARFRACfunction. And this function will return the year fraction of the number of whole days between the start_date and end_date. - start_date is a required argument. So this can be a cell reference or a serial date number referring to the starting date.
- end_date is also a required argument. Similarly, this can be a cell reference or a serial date number representing the end date.
- basis is an optional argument. And it refers to the kind of day count basis to be used.
The Anatomy of the DAY360 Function
The syntax or the way we write the DAY360 function is as follows:
=DAY360(start_date,end_date,[method])
Let’s dissect this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- DAY360() refers to our
DAY360function. And this function will return the number of days between the two dates selected based on the 360-day year. - start_date is one of the dates selected to identify the number of days between the dates. And it is a required argument.
- end_date is the other date selected to identify the number of days between. Also, it is a required argument.
- method is an optional argument. And it refers to a logical value that specifies the calculation method to be used.
Great! We have learned the syntax of the two functions. Now let’s move on and discuss the four methods of how to calculate years of service in Excel.
Using the INT function and the YEARFRAC function
In the first method, we will be utilizing the INT function and the YEARFRAC function to calculate the years of service in our data set. So the INT function is used to round a number down to the nearest integer.
To do this method, let’s learn the step-by-step process below:
1. Firstly, we need to select the cell wherein we will input the years of service. Then, input the formula “=INT(YEARFRAC(B2,C2))”. Lastly, press the ENTER key to return the result.
2. Secondly, drag down the formula to copy it to the other rows to get the years of service.
3. And tada! We have successfully calculated the years of service in Excel.
Using the DATEDIF function
The second method we will try to obtain the years of service is using the DATEDIF function in three ways. Firstly, we will get the years. Then, we can get the years and months. Lastly, we can also get the full years, months, and days result.
To do this, follow the steps below:
1. Firstly, we will obtain the result in years only. So select the cell where we will input the results. Next, type the formula “=DATEDIF(B2,C2,”y”)&” Years””. Lastly, press the Enter key.
Furthermore, drag down to copy the formula to the rest of the rows.
2. Secondly, we can also get the years and months. To do this, select a cell and input the formula “=DATEDIF(B2,C2,”y”)&” Years, “&DATEDIF(B2,C2,”ym”)&” Months””. Then, press ENTER to return the result.
Similarly, copy the formula by dragging it down to the rest of the column.
3. Lastly, we can calculate the years, months, and days. So select a cell and input the formula “=DATEDIF(B2,C2,”y”) & ” Years, ” & DATEDIF(B2,C2,”ym”) & ” Months, ” & DATEDIF(B2,C2,”md”) & ” Days””. Then, press the ENTER key.
And drag down the fill handle to copy the formula.
4. And tada! We have calculated the years of service in years, years and months, and years, months, and days using the DATEDIF function.
Using the IF function and the DATEDIF function
The third method is using the IF function together with the DATEDIF function. And this method is especially appropriate if we are calculating for employees or individuals who worked less than a year.
To apply this method in your work, follow the steps below:
1. Firstly, we need to select the cell wherein we will input the results. Then, we will type in the formula “=IF(DATEDIF(B2,C2,”y”)=0,”Less than a year”,DATEDIF(B2,C2,”y”)&” Years, “&DATEDIF(B2,C2,”ym”)&” Months”)”. Lastly, press ENTER to get the results.
2. Secondly, drag down the fill handle to copy the formula to the rest of the rows.
3. And tada! We have calculated the years of service in Excel for the employees who worked for less than a year.
Using the DAY360 function and the DATE function
Our last method involves using the DAY360 function and the DATE function to obtain the years of service in Excel.
So let us start learning the step-by-step process of this method.
1. Firstly, select a cell and input the formula “=INT(DAYS360(DATE(YEAR(B2),MONTH(B2),DAY(B2)),DATE(YEAR(C2),MONTH(C2),DAY(C2)))/360)”. Then, press the Enter key to get the result.
2. Secondly, copy the formula by dragging down the fill handle.
3. And tada! We have calculated the years of service using the DAY360 function and DATE function in Excel.
You can make your own copy of the spreadsheet above using the link attached below.
And that’s pretty much it! We have explained four simple and efficient methods of how to calculate years of service in Excel. Now you can apply any of the methods whenever you need to.
Are you interested in learning more about what Excel can do? You can now use the YEARFRAC function and DAY360 function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
