How To Calculate Years Of Service In Excel

Suppose you have an employee name who recently resigned from your company. Y'all want to calculate his/her years of services at your company. There are many ways to calculate that. Nosotros tin use many formulas for this. In this article, y'all will encounter the processes on how to calculate years of service in Excel. The length of service with days, months and years together volition also be calculated.

Expect into the below picture to run across what we are going to perform.

Calculate Years of Service betwixt Two Dates

Every bit I said earlier there are many ways to calculate the years of services. Firstly, yous will see this with some simple and short formulas. Look into the below flick where I calculated the years of services for different years by using different formulas.

The formulas which are used here are given below.

• <code> =INT(DAYS360(Date(2014,6,15),DATE(2018,6,27))/360)
• <code> =INT(YEARFRAC(B4,C4))
• <lawmaking> =DATEDIF(B6,C6,"y")

In the first 2 formulas, the INT function has been used in the formulas which requite the integer number avoiding the partial number. In the last formula, nosotros employ an argument "y" for the DATEDIF function which gives the outcome as a full twelvemonth between the two dates.

Summate the Length of Service in Excel betwixt ii specific dates

Now if you desire to calculate the length of service of a person in years, months and days you can use the DATEDIF function. In this example, I calculated the years of service in three ways. The first one gives the output as years, the 2^nd one gives the event as years and months and the iii^rd ane gives the full result with years, months, and days. Look into the below picture where all these results are carried out.

Here the formulas which are used hither are given beneath.

• <code> =DATEDIF(B2, C2, "y")& " Years"
• <code> =DATEDIF(B4,C4,"y")&" Years, "&DATEDIF(B4,C4,"ym")&" Months"
• <code> =DATEDIF(B6,C6,"y") & " Years, " & DATEDIF(B6,C6,"ym") & " Months, " & DATEDIF(B6,C6,"physician") & " Days"

Calculate the Length of Service betwixt a Previous Date and Present Date

At that place is a born function in Excel that can give you the present date. This role is the TODAY function. Information technology is written in Excel as, =TODAY (). This function is categorized as Date/Time function in Excel. It tin can be used in a formula besides. Like, in the previous examples we worked with some random dates. Instead of these random dates if yous want to notice out the length/years of service for a previous date and nowadays date you have to insert the TODAY role instead of the afterwards dates which are placed in cavalcade C. Allow`s expect into the below picture to get a whole idea.

Instead of using the TODAY function in the formula, y'all can apply this in a cell, and in your formula, yous can refer it. The formulas which are used hither are given below.

• <code> =DATEDIF(B2, TODAY(), "y")& " Years"
• <code> =DATEDIF(B4,TODAY(),"y")&" Years, "&DATEDIF(B4,TODAY(),"ym")&" Months"
• <code> =DATEDIF(B6,TODAY(),"y") & " Years, " & DATEDIF(B6,TODAY(),"ym") & " Months, " & DATEDIF(B6,TODAY(),"md") & " Days"

Notation: The advantage of using the TODAY function is that it gets updated every time you open your worksheet. So, every day you lot open your worksheet the length of service besides gets updated.

➥ Related: Calculate Number of Days Between Today & Another Appointment

Download the Working File

Conclusion

In this article, basically, nosotros calculate the number of years between two dates in Excel. The DATEDIF function makes it easier to calculate the length between two dates. Hope y'all did not face up any difficulties while reading this article. Exist connected with usa to become more articles.

---

Farther Readings

• How to calculate working days in Excel excluding weekends & holidays
• Excel Formula to Count Days from Engagement to Today (eight Constructive Ways)
• How to Summate Average Tenure of Employees in Excel
• Number of Days Between Two Dates Computer
• Excel Formula to Count Days from Engagement
• How to Summate Overdue Days in Excel (using Functions)
• How to Add/Subtract Years to a Date in Excel
• How to Discover Number of Months between Two Dates
• Calculate The Difference between Ii Dates in Excel

Source: https://www.exceldemy.com/how-to-calculate-years-of-service-in-excel/

Posted by: cooperowas1981.blogspot.com

Share this post