How to calculate years of service in excel using today
![](https://www.thetechedvocate.org/wp-content/uploads/2023/10/5e86a2b-a7e7-bee2-bb6-67ce7e55c2af_excel_if_between_two_numbers-660x400.webp)
In today’s fast-paced business environment, calculating the years of service for employees is crucial in managing human resources, staff evaluations, and appreciating loyalty and dedication. Excel comes in handy for this purpose, offering a straightforward way to calculate an employee’s tenure using simple formulas. In this article, we will guide you on how to calculate years of service in Excel using today’s date.
Prerequisites:
– Microsoft Excel (version 2010 or later is recommended)
– Employee data stored in an Excel worksheet with their start date
Step-by-Step Guide:
1. Prepare your data: Assuming you have your employee data arranged with column A containing the employee names and column B containing their respective start dates, insert a new header in column C named “Years of Service.”
2. Select an empty cell under the “Years of Service” column where you want to display the calculated years of service for the respective employee.
3. Enter the following formula into the cell:
=DATEDIF(B2,TODAY(),”y”)
In this formula:
– “B2” refers to the first employee’s start date; adjust accordingly if your data starts in a different row.
– TODAY() is a built-in function that dynamically provides today’s date.
– “y” represents the unit (years) you want as output.
4. Press Enter to display the result. The cell now shows an employee’s years of service rounded down to completed years.
5. To fill in the years of service for other employees, click on the lower right corner of the initial calculation cell, also known as the fill handle and drag it down to cover all relevant rows in your list.
6. Save your changes.
Now you have successfully calculated years of service for your employees using today’s date in Excel!
This method is effective and easy-to-understand but keep in mind that DATEDIF is an undocumented function in Excel and may not appear in the list of formulas. However, it works perfectly for this task and helps you keep track of employee’s years of service with minimal effort.