Kathy, an HR Manager in the Phoenix area, asked, “How do I setup a spreadsheet to calculate years of employment?”
Using simple Excel date functions will provide this calculation.
First, let’s talk about a couple of basic date formulas.
The “TODAY” function updates each and every day you open the spreadsheet.
For example, in an Excel spreadsheet I created yesterday, I typed “=TODAY()” in Cell A1. Yesterday, upon the creation, the date result was “08/19/2010.” When I open the spreadsheet today, the date result in Cell A1 is now “08/20/2010.” The cell dynamically updated based on the calendar date on my computer.
If you want to get even more specific, you can use the following function:
The “NOW” function updates each and every minute, based on the formatting of the cell. If you format the time to include seconds, then the cell updates each and every second, upon updating another portion of the spreadsheet.
For example, if I place the formula “=NOW()” in Cell A2, each time I update another portion of the spreadsheet, Cell A2 will update the result to the current time. This function allows for using different styles of the date formatting.
Now that we have two basic date functions described, let’s answer Kathy’s question.
To find out the time an employee has been employed at a company, setup the spreadsheet with at least the following two columns.
Column B = Hire Date
Column C = Years with company
Use Column A for the employee name and add any other columns you need.
Column B, obviously, will be input by the HR Department upon hire.
In Column C, type the formula as such:
The “DATEDIF” function calculates the difference between two dates. Copy the formula down all rows, as needed.
In the example above, the function calculates the difference between the “Hire Date” and “Today” and formats the difference in “Years.” See the slideshow for graphics.
Changing the “Y” in the formula to “M” changes the calculation to provide the result in number of months, instead of years. “D” provides the result in number of days.
Since we used the “TODAY()” function within the “DATEDIF” function, the result updates each and every time you open the spreadsheet.
Practice using different formats to find the best result for your company. Any way you use them, these Excel date functions are sure to be helpful for an HR Department.