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.

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

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

Step1: In E2, enter the formula:

=DATEDIF(C2,D2,"y")&" years "&DATEDIF(C2,D2,"ym")&" months"

Comments:

• DATEIF function returns the duration from two dates, it has three parameters like DATEDIF(start_date,end_date,unit).
• 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).
• We use & to combine DATEIF function returned values and string “years” and “months” to make result looks reasonable.

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

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

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.

Step1: 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.

Step2: 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.

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.

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

Step1: Only calculate the length of service with Year unit. Enter the formula:

=DATEDIF(C2,D2,"y")&" years "

Step2: Only calculate the length of service with Month unit. Enter the formula:

=DATEDIF(C2,D2,"m")&" months "

Step3: Only calculate the length of service with Date unit. Enter the formula:

=DATEDIF(C2,D2,"d")&" dates "

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.

4. Video: Calculate the Length of Service from Join Date to Current Date in Excel

This video demonstrates the formula you can use in Excel to calculate the length of service from the join date to the current date.