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.

**âžĄ Read More: Calculate Number of Days Between Today & Another Date**

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

## Further Readings

**How to calculate working days in Excel excluding weekends & holidays****Excel Formula to Count Days from Date to Today (8 Effective Ways)****How to Calculate Average Tenure of Employees in Excel****Number of Days Between Two Dates Calculator****Excel Formula to Count Days from Date****How to Calculate Overdue Days in Excel (using Functions)****How to Add/Subtract Years to a Date in Excel****How to Find Number of Months between Two Dates****Calculate The Difference between Two Dates in Excel**

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!