Skip to main content

Explore the new, free version of Kutools for Outlook today!

How to calculate the length of service from hire date in Excel?

Author: Xiaoyang Last Modified: 2020-04-29

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.

Calculate the length of service from the hire date with YEARFRAC function

Calculate the length of service from the hire date with DATEDIF function

Calculate the length of service from the hire date to today with DATEDIF function

Calculate the length of service from the hire date with an amazing feature

Calculate the length of service from the hire date with YEARFRAC function

To calculate the length of service from the hire date and a specific date, the YEARFRAC function can help you.

Please enter or copy the following formula into a blank cell:

=INT(YEARFRAC(B2,C2))

Note: In the above formula, B2 is the hire date cell and C2 is the end date cell.

Then drag the fill handle to copy the fromula to other cells you need, and you will get the person's lenght of service in years.

Calculate the length of service from the hire date with DATEDIF function

If you need to calculate the length of service in exact years months and days, the DATEDIF function in Excel can do you a favor.

Calculate the length of service in years:

Please enter or copy the following formula into a blank cell:

=DATEDIF(B2, C2, "y")& " Years"

Calculate the length of service in years and months:

Please apply the following formula:

=DATEDIF(B2,C2,"y")&" Years, "&DATEDIF(B2,C2,"ym")&" Months"

Calculate the length of service in years, months and days:

Apply the following formula:

=DATEDIF(B2,C2,"y") & " Years, " & DATEDIF(B2,C2,"ym") & " Months, " & DATEDIF(B2,C2,"md") & " Days"

Note: In the above formula, B2 is the hire date cell and C2 is the end date cell.

Calculate the length of service from the hire date to today with DATEDIF function

Someitmes, you may want to calculate the length of service from hire date to today, so, every day you open the worksheet the length of service will be updated automatically. In this case, you should combine the TODAY function within the DATEDIF function.

Please apply any one below formulas to your need:

=DATEDIF(B4, TODAY(), "y")& " Years"
=DATEDIF(B4,TODAY(),"y")&" Years, "&DATEDIF(B4,TODAY(),"ym")&" Months"
=DATEDIF(B6,TODAY(),"y") & " Years, " & DATEDIF(B4,TODAY(),"ym") & " Months, " & DATEDIF(B4,TODAY(),"md") & " Days"

Calculate the length of service from the hire date with an amazing feature

It is somewhat difficult for you to remember so many formulas, but, if you have Kutools for Excel, with its Date & Time Helper feature, you can quickly calculate the length of service in years, months, days, years + months + days or other formats as you need.

Note:To apply this Date & Time Helper, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click a cell where you want to locate the result, and then click Kutools > Formula Helper > Date & Time helper, see screenshot:

2. In the Date & Time Helper dialog box, do the following operations:

  • Check Difference option from the Type section;
  • In the Arguments input textboxes, select the cells which contain the hire date and end date separately;
  • Then, choose the output result type as you need. For example, you can get the length of service in years, months, years + months + days, years + months and so on.

3. After finishing the settings, click OK button, and you will get the first calculated result, then, you just need to drag the fill handle to fill this formula to test cells, and now you will get the following result as you need:

Click to Download Kutools for Excel and free trial Now!

More relative articles:

  • Count The Number Of Days / Workdays / Weekends Between Two Dates
  • Have you ever needed to count the number of days between two dates in Excel? May be, sometimes, you just only want to calculate the workdays between two dates, and sometime, you need to count the weekend days only between the two dates. How could you count the number of days between two dates in a certain condition?
  • Calculate Days Hours And Minutes Between Two Dates
  • Supposing, you have two columns of date time cells, and now, you want to calculate the difference in days, hours and minutes between these two date time cells as following screenshot shown. This article, I will talk about some methods to solve this task for you.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (20)
Rated 5 out of 5 · 1 ratings
Biagio
Guest
This comment was minimized by the moderator on the site
First, thank you very much for helping us!

I am trying to use the formula for years and months of service (=DATEDIF(B2,C2,"y")&" Years, "&DATEDIF(B2,C2,"ym")&" Months") which works great, but I would like to add that line and another line that just has numbers in it. Is that possible? For instance: The formula =DATEDIF(B2,C2,"y")&" Years, "&DATEDIF(B2,C2,"ym")&" Months" is located in D2 and I would like to add number of years and months of service from another agency in E2 with the total sum in F2.

All assistance is great appreciated.
Reply
0
0
Rated 5 out of 5
Sarah
Guest
This comment was minimized by the moderator on the site
If I have a mixed list of employees still with the company (with start date only) and employees no longer with the company (with start and end date), is there a formula for calculating length of service using current date unless there is an end date, in which case the end date would be used in place of the current date to calculate total length of service from start to end?
Reply
0
0
Paul Agbo Nicholas
Guest
This comment was minimized by the moderator on the site
what version of Excel can i get this? I am unable to get the function on Excel 2016
Reply
0
0
MandyZhou
MandyZhou     Paul Agbo Nicholas
This comment was minimized by the moderator on the site
Hello,
The Excel version in the example is Excel 2010. The DATEDIF function is not available in Excel 2013 and later versions.
DATEDIF is not a standard function and hence not part of functions library and so no documentation. Microsoft doesn't promote to use this function as it gives incorrect results in few circumstances.

This function does not appear in the 'Insert Function' dialog box and is not recognised in the 'Function Arguments' dialog box.
The accuracy of this function was improved slightly in Excel 2010 but some of the bugs were never fixed.

Hope it helps. Have a nice day.
Sincerely,
Mandy
Reply
Report
0
0
Candace C Ford
Guest
This comment was minimized by the moderator on the site
After the years of service has been determined. How do you calculate the persons service computation date (SCD). Is there a formula to subtract the years of service from the persons new appointment date to get their SCD leave date?
Reply
0
0
Ali Akbar
Guest
This comment was minimized by the moderator on the site
i m in problem; I am new joining to Govt. dept. as Junior Clerk in Accounts site: I want to calculate to make service end period, and after to add extra months years days in service period given by government relaxation period to complete service 9 to 10 years as (the total service is 8 year, 8 months, 18 days) but govt. give 10 months and 28 days to compile the service period for allowing the come in to as pensioner. Thanks to you for solve that problem.
Reply
0
0
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations