Suppose you have an employee who recently resigned from your company. You want to calculate his/her** years of service** at your company. There are several ways to calculate that. We can use many formulas for this. The main objective of this article is to explain how to **calculate years of service** in Excel in different ways. The service duration with days, months, and years will also be calculated.

## Download Practice Workbook

## 4 Easy Ways to Calculate Years of Service in Excel

As I said earlier there are several ways to **calculate years of service** in Excel. Here, I will explain **4** easy and efficient ways of doing it. I have taken the following dataset to explain this article. It contains the **Employee Name**, **Joining Date**, and** End Date**. I will explain how to calculate **Years** **of Service** for them.

### 1. Using INT & YEARFRAC Functions to Calculate Years of Service

In this method, I will explain how you can use **the INT function** and **the YEARFRAC function** to **calculate years of service **in Excel. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Years of Service**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=INT(YEARFRAC(C5,D5))`

**Formula Breakdown**

**YEARFRAC(C5,D5) —->**Here, the**YEARFRAC**function will return a fraction of the year represented by the number of days between the dates in cells**C5**and**D6**.**Output: 7.94166666666667**

**INT(YEARFRAC(C5,D5))****—->**turns into**INT(7.94166666666667) —->**Here, the**INT**function will return the integer number by rounding it down.**Output: 7**

- Thirdly, press
**ENTER**to get the result.

- After that, drag the
**Fill Handle**to copy the formula to other cells.

Finally, you can see that I have copied the formula to all the other cells and calculated **Years of Service **in Excel for every employee.

### 2. Employing DAYS360 and DATE Functions to Calculate Years of Service

In this second method, I will use **the DAYS360 function** and **the DATE function** to **calculate years of service** in Excel. Let me show you how it is done.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Years of Service**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=INT(DAYS360(DATE(YEAR(C5),MONTH(C5),DAY(C5)),DATE(YEAR(D5),MONTH(D5),DAY(D5)))/360) `

**Formula Breakdown**

**DAY(D5) —->**Here,**the DAY function**will return the day number of the date in cell**D5**.**Output: 24**

**MONTH(D5)****—->**Here,**the MONTH function**will return the month number of the given date in cell**D5**.**Output: 1**

**YEAR(D5) —->**Here,**the YEAR function**will return the year number of the given date in cell**D5**.**Output: 2022**

**DATE(YEAR(D5),MONTH(D5),DAY(D5)) —->**turns into**DATE(2022,1,24)****—->**Here, the**DATE**function will return a serial number that represents a date from a given year, month, and day.**Output: 44585**

**DATE(YEAR(C5),MONTH(C5),DAY(C5)) —->**turns into**DATE(2014,2,15) —->**Again, the**DATE**function will return a serial number that represents a date from a given year, month, and day.**Output: 41685**

**DAYS360(DATE(YEAR(C5),MONTH(C5),DAY(C5)),DATE(YEAR(D5),MONTH(D5),DAY(D5))) —->**turns into**DAYS360(41685,44585) —->**Here, the**DAYS360**function will return the number of days between the two given dates.**Output: 2859**

**INT(DAYS360(DATE(YEAR(C5),MONTH(C5),DAY(C5)),DATE(YEAR(D5),MONTH(D5),DAY(D5)))/360) —->**turns into**INT(2859/360) —->**Here, Here, the**INT**function will return the integer number by rounding it down.**Output: 7**

- Thirdly, press
**ENTER**to get the result.

- After that, drag the
**Fill Handle**to copy the formula to other cells.

Finally, you can see that I have copied the formula to all the other cells and calculated **Years of Service** for every employee.

### 3. Use of DATEDIF Function to Calculate Years of Service in Excel

Now, if you want to calculate the service duration of a person in years, months and days you can use **the DATEDIF function**. In this example, I will calculate the years of service in **three **ways. The **1**** ^{st}** one will give the output as

**years**, the

**2**

**one will give the result as**

^{nd}**years and months**and the

**3**

**one will give the full result as**

^{rd}**years, months, and days**.

#### 3.1. Using DATEDIF Function to Calculate Years

In this method, I will use the **DATEDIF** function to calculate the **Year of Service** in **years**. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Years of Service**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=DATEDIF(C5, D5, "y")& " Years"`

**Formula Breakdown**

**DATEDIF(C5, D5, “y”) —->**Here, the**DATEDIF**function will return the number of years between the two given dates.**Output: 7**

**DATEDIF(C5, D5, “y”)& ” Years” —->**turns into**7& ” Years” —->**Now, the**Ampersand (&)**operator will combine the**text**and**formula**.**Output: “7 Years”**

- Thirdly, press
**ENTER**to get the result.

- Now, drag the
**Fill Handle**to copy the formula.

Finally, you can see that I have copied the formula to all the other cells and calculated the **Years of Service** in Excel for every employee in **years**.

#### 3.2. Applying DATEDIF Function to Calculate Years and Months

Here, I will apply the **DATEDIF** function to calculate the **Years of Services** in **years** **and months**. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Years of Service**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=DATEDIF(C5,D5,"y")&" Years, "&DATEDIF(C5,D5,"ym")&" Months"`

**Formula Breakdown**

**DATEDIF(C5,D5,”y”) —->**Here, the**DATEDIF**function will return the number of years between the two given dates.**Output: 7**

**DATEDIF(C5,D5,”ym”) —->**Here, the**DATEDIF**function will return the number of months between the two given dates ignoring the days and years.**Output: 11**

**DATEDIF(C5,D5,”y”)&” Years, “&DATEDIF(C5,D5,”ym”)&” Months” —->**turns into**7&” Years, “&11&” Months”****—->**Now, the**Ampersand (&)**operator will combine the**texts**and**formulas**.**Output: “7 Years, 11 Months”**

- Thirdly, press
**ENTER**to get the result.

- After that, drag the
**Fill Handle**to copy the formula.

Finally, you can see that I have copied the formula to all the other cells and calculated the **Years of Service** for every employee in **years and months**.

#### 3.3. Use of DATEDIF Function to Calculate Years, Months, and Days

In this method, I will use the **DATEDIF** function to calculate the **Year of Service** in EXcel in **years, months, and days**. Let’s see how it is done.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Years of Service**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=DATEDIF(C5,D5,"y") & " Years, " & DATEDIF(C5,D5,"ym") & " Months, " & DATEDIF(C5,D5,"md") & " Days"`

**Formula Breakdown**

**DATEDIF(C5,D5,”y”)****—->**Here, the**DATEDIF**function will return the number of years between the two given dates.**Output: 7**

**DATEDIF(C5,D5,”ym”)****—->**Here, the**DATEDIF**function will return the number of months between the two given dates ignoring the days and years.**Output: 11**

**DATEDIF(C5,D5,”md”) —->**Here, the**DATEDIF**function will return the number of days between the two given dates ignoring the months and years.**Output: 9**

**DATEDIF(C5,D5,”y”) & ” Years, ” & DATEDIF(C5,D5,”ym”) & ” Months, ” & DATEDIF(C5,D5,”md”) & ” Days” —->**turns into**7 & ” Years, ” & 11 & ” Months, ” & 9 & ” Days”****—->**Now, the**Ampersand (&)**operator will combine the**texts**and**formulas**.**Output: “7 Years, 11 Months, 9 Days”**

- Thirdly, press
**ENTER**to get the result.

- After that, drag the
**Fill Handle**to copy the formula.

Finally, you can see that I have copied the formula to all the other cells and calculated the **Years of Service** for every employee in** years, months, and days**.

### 4. Employing IF and DATEDIF Functions

If you have employees who have worked less than a year then this method will be helpful for you. Here, I will use **the IF function** and the **DATEDIF **function to calculate the **Years of Service** in Excel. I will explain** 2 **examples with **2** different types of **output**.

#### Example-01: Returning Text String If Service Duration Is Less Than One Year

In this example, I will return a** text string** if the **Years of Service** is **less than a year**. Let’s see how it is done.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Years of Service**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=IF(DATEDIF(C5,D5,"y")=0,"Less than a year",DATEDIF(C5,D5,"y")&" Years, "&DATEDIF(C5,D5,"ym")&" Months")`

**Formula Breakdown**

**DATEDIF(C5,D5,”y”) —->**Here, the**DATEDIF**function will return the number of years between the two given dates.**Output: 7**

**DATEDIF(C5,D5,”ym”)****—->**Here, the**DATEDIF**function will return the number of months between the two given dates ignoring the days and years.**Output: 11**

**IF(DATEDIF(C5,D5,”y”)=0,”Less than a year”,DATEDIF(C5,D5,”y”)&” Years, “&DATEDIF(C5,D5,”ym”)&” Months”) —->**turns into**IF(7=0,”Less than a year”,7&” Years, “&11&” Months”)****—->**Now, the**IF**function will check the**logical_test**. If it is**True**, the formula will return**“Less than a year”.**And if it is**False**the formula will return the**Years of Service**in years and months.**Output: “7 Years, 11 Months”**

- Thirdly, press
**ENTER**to get the result.

- After that, drag the
**Fill Handle**to copy the formula.

Finally, you can see that I have copied the formula and calculated the **Years of Service **for every employee.

#### Example-02: Calculating Month If Service Duration Is Less Than One Year

In this example, I will calculate the **Years of Service** in **months** if it is **less than a year**. I will use the **IF** function and the **DATEDIF** function to calculate the **Years of Service **in Excel. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Years of Service**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=IF(DATEDIF(C5,D5,"y")=0,DATEDIF(C5,D5,"ym")&" Months",DATEDIF(C5,D5,"y")&" Years, "&DATEDIF(C5,D5,"ym")&" Months")`

**Formula Breakdown**

**DATEDIF(C5,D5,”y”) —->**Here, the**DATEDIF**function will return the number of years between the two given dates.**Output: 7**

**DATEDIF(C5,D5,”ym”)****—->**This**DATEDIF**function will return the number of months between the two given dates ignoring the days and years.**Output: 11**

**IF(DATEDIF(C5,D5,”y”)=0,DATEDIF(C5,D5,”ym”)&” Months”,DATEDIF(C5,D5,”y”)&” Years, “&DATEDIF(C5,D5,”ym”)&” Months”) —->**turns into**IF(7=0,11&” Months”,7&” Years, “&11&” Months”) —->**Now, the**IF**function will check the**logical_test**. If it is**True**, the formula will return the**Years of Service**in months**.**And if it is**False**the formula will return the**Years of Service**in years and months.**Output: “7 Years, 11 Months”**

- Thirdly, press
**ENTER**to get the result.

- Next, drag the
**Fill Handle**to copy the formula.

Finally, you can see that I have copied the formula and calculated the **Years of Service **for every employee.

## Calculate Years of Service in Excel from Hire Date

In this section, I will show you how you can **calculate Years of Service **from the** Hire Date** to the **current date**. I will also show you how you can calculate the **End Date** of a service period from the **Hire Date**.

### 1. Using TODAY Function to Calculate Years of Service from Hire Date

There is a built-in function in Excel that can give you the **current date**. This function is **the TODAY function**. It is written in Excel as, **=TODAY ()**. This function is categorized as **Date/Time** function in Excel. It can be used in a formula also. Like, in the previous examples we worked with the **End Date**. Instead of the **End Date**, if you want to find out the **Years of Service** from the **Hire Date **to the **Current Date **you have to insert the **TODAY** function instead of the** End Date**.

Let me show you the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Years of Service**. Here, I selected cell**D5**. - Secondly, in cell
**D5**write the following formula.

`=DATEDIF(C5,TODAY(),"y") & " Years, " & DATEDIF(C5,TODAY(),"ym") & " Months, " & DATEDIF(C5,TODAY(),"md") & " Days"`

**Formula Breakdown**

**DATEDIF(C5,TODAY(),”y”) —->**Here, the**DATEDIF**function will return the number of years between the**Hire Date**and the**Current Date**.**Output: 8**

**DATEDIF(C5,TODAY(),”ym”) —->**The**DATEDIF**function will return the number of months between the**Hire Date**and the**Current Date**ignoring the days and years.**Output: 6**

**DATEDIF(C5,TODAY(),”md”)****—->**Here, the**DATEDIF**function will return the number of days between the**Hire Date**and the**Current Date**ignoring the months and years.**Output: 22**

**DATEDIF(C5,TODAY(),”y”) & ” Years, ” & DATEDIF(C5,TODAY(),”ym”) & ” Months, ” & DATEDIF(C5,TODAY(),”md”) & ” Days”****—->**turns into**8 & ” Years, ” & 6 & ” Months, ” & 22 & ” Days” —->**Now, the**Ampersand (&)**operator will combine the**texts**and**formulas**.**Output: “8 Years, 6 Months, 22 Days”**

*The advantage of using the*

**Note:****TODAY**function is that it gets updated every time you open your worksheet. So, every day you open your worksheet the length of service also gets updated.

- Thirdly, press
**ENTER**to get the result.

- After that, drag the
**Fill Handle**to copy the formula.

Finally, you can see that I have copied the formula and calculated the **Years of Service **from **Hire Date** in Excel for every employee.

### 2. Calculating End Date from Hire Date after Certain Years of Service

In this example, I will show you how you can calculate the **End Date** of a service period from the **Hire Date** and the **Years of Service**. Suppose, you have some employees and you want to evaluate their performance after a certain **Years of Service** from the** Hire Date**. So, for this performance evaluation, you will need the** End Date **of that service period. Here, I will use **the EDATE function** to calculate the **End Date**.

Let’s see how you can do it.

**Steps:**

- Firstly, select the cell where you want to calculate the
**End Date**. Here, I selected cell**E5.** - Secondly, in cell
**E5**write the following formula.

`=EDATE(C5,D5*12) `

Here, in the **EDATE** function, I selected **C5 **as **start_date** and **D5*12** as **month**s. I **multiplied** the **years** by **12** to convert them into **months**. Afterward, the formula will return the date after these selected months.

- Thirdly, press
**ENTER**, and you will get the**End Date**.

- After that, drag the
**Fill Handle**to copy the formula.

Finally, you can see that I have copied the formula and got the **End Date** for every employee.

## Practice Section

Here, I have provided a practice sheet for you so that you can practice how to **calculate years of service **in Excel.

## Conclusion

To conclude, I tried to cover how to **calculate years of service** in Excel. Basically, I calculated the number of years between two dates in Excel. I explained** 4** different ways of doing it. The **DATEDIF** function makes it easier to calculate the length between two dates. Hope you did not face any difficulties while reading this article. Be connected with **ExcelDemy** to get more articles like this. Lastly, if you have any questions, let me know in the comment section.

Very useful formula for HR Professionals.

Thanks Chamila for your feedback!

This was very helpful! Great information! Thank you very much!

Thanks so much! This made things a whole lot easier for me. As Chamila said, very useful formula for HR folks.

Thanks for your feedback, LuluBelle! Glad to know that it helped.

This formula has a bug. If you input joining date of 01/01/20, and a present date of 31/12/20, it returns 0years, 11 Months, 30 Days. However this is actually 1 year, 0 months and 0 days.

Hi Justin,

Thanks for notifying this. I will check and update the article.

Regards

I want to subscribe

THANK YOU VERY MUCH for sharing!