Suppose you have an employee name who recently resigned from your company. You want to calculate his/her years of services at your company. There are many ways to calculate that. We can use many formulas for this. In this article, you will see the processes on how to calculate years of service in Excel. The length of service with days, months and years together will also be calculated.
Look into the below picture to see what we are going to perform.
Table of Contents hideCalculate Years of Service between Two DatesCalculate the Length of Service in Excel between two specific datesCalculate the Length of Service between a Previous Date and Present DateDownload the Working FileConclusionFurther Readings
Calculate Years of Service between Two Dates
As I said earlier there are many ways to calculate the years of services. Firstly, you will see this with some simple and short formulas. Look into the below picture where I calculated the years of services for different years by using different formulas.
The formulas which are used here are given below.
In the first 2 formulas, the INT function has been used in the formulas which give the integer number avoiding the fractional number. In the last formula, we use an argument y for the DATEDIF function which gives the result as a full year between the two dates.
Calculate 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 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 2nd one gives the result as years and months and the 3rd one 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 here are given below.
- <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,md) & Days
Calculate the Length of Service between a Previous Date and Present Date
There is a built-in function in Excel that can give you the present 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 some random dates. Instead of these random dates if you want to find out the length/years of service for a previous date and present date you have to insert the TODAY function instead of the later dates which are placed in column C. Let`s look into the below picture to get a whole idea.
Instead of using the TODAY function in the formula, you can use this in a cell, and in your formula, you can refer it. The formulas which are used here 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) & DaysNote: The advantage of using the 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.
Related: Calculate Number of Days Between Today & Another Date
Download the Working FileHow to Calculate Years of Service in Excel
In this article, basically, we calculate the number of years between two dates in Excel. 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 us to get more articles.
- 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