# How to Calculate Years of Service in Excel

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 `YEARFRAC` function. 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 `DAY360` function. 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.

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. 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. Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

##### You May Also Like ## How to Use XLOOKUP in Excel

This guide will discuss how to use XLOOKUP in Excel. The rules for using the XLOOKUP in Excel… ## How to Do Conditional Formatting for Blank Cells in Excel

This guide will explain how to do conditional formatting for blank cells in Excel using two simple methods.… ## How To Use AVERAGEIF With Multiple Ranges in Excel

This guide will explain how to use the AVERAGEIF function with multiple ranges in Excel. We will present… ## How to Calculate Median of Filtered Rows in Excel

This guide will explain how to calculate the median of filtered rows in Excel using the AGGREGATE function.…  