How to Calculate the Length of Service from Join Date to Current Date in Excel?

In previous article, we have learned how to calculate the hire date from birth date in excel, this time we learn something else. Suppose we have a company and several employees, and we want to count the length of service for each employee from their join date to current date, then we will know their years of work experience in our company. So, we need to know the formula to calculate the ‘Duration’. This article aims at to tell you how to calculate length of service by a simple formula, please read it and I’m sure you can get what you want.

To demonstrate the formula clearly, we create a table to show the details.

Calculate the Length of Service 1

Suppose we have a company with only above five employees and their join dates are different. We want to calculate the ‘Service Duration’ comparing to current date for everyone, actually we need to know the period between ‘Join Date’ and ‘End Date’.

Part 1: Calculate Length of Service to Year & Month Unit in Excel


Step 1: In E2, enter the formula =DATEDIF(C2,D2,”y”)&” years “&DATEDIF(C2,D2,”ym”)&” months”.

Calculate the Length of Service 2

Comments:

  1. DATEIF function returns the duration from two dates, it has three parameters like DATEDIF(start_date,end_date,unit).
  2. In this case, for the first DATEIF function, the unit is ‘y’, so it returns Year duration between start date and end date; for the second DATEIF function, the unit is ‘ym’, it returns the Month duration between start date and end date (ignore Year part).
  3. We use & to combine DATEIF function returned values and string “years” and “months” to make result looks reasonable.

Step 2: Click Enter to get result. See, the length of service for Keith is 25 years 0 months. This result looks very clearly.

Calculate the Length of Service 3

Step 3: Drag the fill handle to fill cells for other employees in this table.

Calculate the Length of Service 4

Part 2: Calculate Length of Service to Year & Month & Date Unit in Excel


You can see that in above table, though we can get a rough value about the duration, if we want to get the exact length of service, like calculate the duration end to Date unit, you can change your formula correspondingly.

Step 1: In E2, enter the formula =DATEDIF(C2,D2,”y”)&” years “&DATEDIF(C2,D2,”ym”) &” months “&DATEDIF(C2,D2,”md”) &” dates”. This time, we add date in the formula, then we can get an exact value of service duration. Make sure that for the third DATEIF function, the unit is ‘md’, otherwise the date is calculated improperly.

Calculate the Length of Service 5

Step 2: Click Enter to get result. Verify that ‘X dates’ is added in duration this time. We can repeat above step#3 to fill the other cells properly.

Calculate the Length of Service 6

Comment:

If you enter the formula like =DATEDIF(C2,D2,”y”)&” years “&DATEDIF(C2,D2,”ym”) &” months “&DATEDIF(C2,D2,”d”) &” dates”, you will get below result improperly. So, you must enter correct unit ‘md’ in formula.

Calculate the Length of Service 7

Part 3: Calculate Length of Service to Year/Month/Date Unit in Excel


Step 1: Only calculate the length of service with Year unit. Enter the formula =DATEDIF(C2,D2,”y”)&” years “.

Calculate the Length of Service 8

Step 2: Only calculate the length of service with Month unit. Enter the formula =DATEDIF(C2,D2,”m”)&” months “.

Calculate the Length of Service 9

Step 3: Only calculate the length of service with Date unit. Enter the formula =DATEDIF(C2,D2,”d”)&” dates “.

Calculate the Length of Service 10

Now you already know the ways to calculate service of length by different units. You can also edit your own formula in your work to suit your demands accordingly.

 

 

Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Excel Array Matrix Operation

In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the ...

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference ...

Sidebar