How to calculate the length of service from hire date in Excel?
If you have a range of data including employee names and their date of joining your company in a worksheet, now, you may want to calculate their length of service, it means to get how many years and months an employee has worked for your company. This article, I will talk about quick method to solving this job for you.
Add a number of years / months / days / weeks to date:
Kutools for Excel’s Formula Helper utility contains some commonly-used formulas, such as add years to date, calculate age based on birthday and so on. With these usual formulas, you can quickly get the result that you want without remembering any formulas.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
For example, I have the following data of names and their hire date and end date, with a formula, you can finish it quickly.
Please enter this formula: =DATEDIF(B2,C2,"y") & " years " & DATEDIF(B2,C2,"ym") & " months " into a blank cell beside your data range, E2, for instance, and then drag the fill handle down to the cells that you want to apply this formula, and all years and months which employees have worked in a company are calculated at once, see screenshot:
Note: In the above formula, B2 is the start date cell and C2 is the end date cell, you can change them to your need.
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 7 months agoHow can I calculate years of service from the date or hire to today?
To post as a guest, your comment is unpublished.· 7 months agoHello, AB,
To calculate years of service from the hire date to today, you just need to apply the following formula: (Note: A2 is the hire date, change it to your need)
=DATEDIF(A2,TODAY(),"y") & " years " & DATEDIF(A2,TODAY(),"ym") & " months "
Please try it, hope it can help you!