Calculate seniority is a more complex operation than it seems. But Excel has a special function to calculate this.
Problem when you substract 2 dates in Excel
Whatever the software you will use, Excel or any other spreadsheet, subtract 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
Of course, it's more convenient to return the result in months and days, like 1 month and 8 days.
But if you use the function MONTH to calculate the difference in months, you will return the result 2
So how to calculate the 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 number of days
- m returns the difference in number of months
- y returns the difference in 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.
For the number of days once a full month has expired the code is md
So in this example, the difference between the 2 dates is 1 year, 10 months and 22 days.
Calculate the seniority for employees
Now, let's say you have this document with the employment date for employees Now, with the previous formula, it's easy to calculate seniority.
In 3 different columns, we will return the calculation for the years, the months in the year and the number of days in the month.
You can combine the 3 formulas in the same cell. But this time, the result will be a text and not a number anymore