How To Calculate Years And Months Of Service In Excel

Suppose you have an employee name who recently resigned from your company. You want to summate his/her years of services at your visitor. There are many ways to calculate that. We can utilise many formulas for this. In this article, you volition see the processes on how to calculate years of service in Excel. The length of service with days, months and years together will also exist calculated.

Look into the below picture to see what we are going to perform.

Calculate Years of Service between Two Dates

Equally I said earlier there are many ways to summate the years of services. Firstly, you will see this with some uncomplicated and short formulas. Look into the below picture where I calculated the years of services for unlike years by using unlike formulas.

The formulas which are used hither are given below.

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

In the offset 2 formulas, the INT function has been used in the formulas which give the integer number avoiding the fractional number. In the concluding formula, nosotros use an argument "y" for the DATEDIF office which gives the issue as a full year between the two dates.

Summate the Length of Service in Excel between two specific dates

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

Here the formulas which are used here are given below.

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

Summate the Length of Service betwixt a Previous Appointment and Nowadays Date

There is a built-in function in Excel that can give you the present appointment. This function is the TODAY role. It is written in Excel equally, =TODAY (). This part is categorized every bit Appointment/Time function in Excel. It can be used in a formula besides. Like, in the previous examples we worked with some random dates. Instead of these random dates if you want to find out the length/years of service for a previous date and nowadays appointment y'all have to insert the TODAY part instead of the after dates which are placed in column C. Permit`s await into the below picture to get a whole idea.

Instead of using the TODAY function in the formula, you tin can use this in a prison cell, and in your formula, you can refer information technology. The formulas which are used here are given below.

<lawmaking> =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"

Note: The advantage of using the TODAY part 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.

➥ Related: Calculate Number of Days Betwixt Today & Another Date

Download the Working File

Conclusion

In this commodity, basically, we calculate the number of years betwixt two dates in Excel. The DATEDIF office makes information technology easier to calculate the length betwixt two dates. Hope you did non confront whatever difficulties while reading this commodity. Be connected with us to get more articles.