This guide will explain **how to calculate years of service in Excel using four simple and efficient methods**.

##### Table of Contents

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.

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.