Calculating seniority is a more complex operation than it seems. But Excel has a special function to calculate this 😉
What is the problem when you subtract 2 dates in Excel
Whatever the software you will use, Excel or any other spreadsheet, subtracting 2 dates is always the source of mistakes.
- For instance, we want to subtract the date 08/10/2021 from 30/08/2021. The result is 39 days
- Or, it's 1 month and 8 days.
- But if you use the MONTH function, MONTH(08/10/2021) - MONTH(30/08/2021) = 2 !!!!!!!!!!!
So how to calculate seniority?
How to Calculate Seniority by Formula in Excel?
Fortunately, there is an Excel formula that takes all these difficulties into account to simplify the calculation. It's the DATEDIF function. The problem with this function is that it is a hidden function. The function exists but it is not displayed in the list of Excel functions. Also, there is no tooltip when you use this function.
How to use the DATEDIF function
The DATEDIF function needs 3 parameters
- Date 1
- Date 2 (must be greater than Date 1)
- The type of deviation calculation
The 3rd argument is the most important because it is it which will condition the calculation of the difference between the 2 dates
- d will return the difference in the number of days
- m returns the difference in the number of months
- y returns the difference in the number of full years
But, the most interesting with the DATEDIF function is to calculate the number of months included in a year (therefore between 0 and 11 months). To do this, you must indicate as a parameter the code ym; the number of months after a full year has expired.
=DATEDIF(B1,B2,"ym")
For the number of days once a full month has expired the code is md
=DATEDIF(B1,B2,"md")
So in this example, the difference between the 2 dates is 1 year, 10 months, and 22 days.
Calculate the seniority for each employee in Excel
Now, if you have a document showing the Hiring date for each employee, you can easily extract the year, month and day with the previous formulae.
You can combine the 3 formulas in the same cell with the & sign. But this time, the result will be a text and not a number anymore