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.

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. Combining Excel 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.

**Related Content:** How to Calculate Elapsed Time in Excel

## 2. Joining DAYS360 and DATE Functions in Excel 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. Using the 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^{nd} one will give the result as years and months and the 3^{rd} one will give the full result as years, months, and days.

### 3.1. Applying Excel 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**.

**Related Content: **How to Calculate Slack Time in Excel

### 3.2. Using the 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 Inserting 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. Merging Excel IF and DATEDIF Functions to Calculate Service Years

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.

### 4.1 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.

### 4.2 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 Excel

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.

**Read More:** How to Calculate Cycle Time in Excel

### 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.

**Download Practice Workbook**

## Conclusion

To conclude, I tried to cover how to calculate years of service in Excel. 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. 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!