# 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. 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”. 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. Step 3: Drag the fill handle to fill cells for other employees in this table. ## 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. 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. 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. ## 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 “. Step 2: Only calculate the length of service with Month unit. Enter the formula =DATEDIF(C2,D2,”m”)&” months “. Step 3: 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.

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

If Cell is Blank

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Sidebar