Date

Calculate Seniority in Excel

Reading Time: 2 minutes

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.

The DATEDIF function is not listed in the dialog box

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
Difference between 2 dates with DATEDIF

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")

Difference in year months days with DATEDIF

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.

Calculate seniority with hiring dates

You can combine the 3 formulas in the same cell. But this time, the result will be a text and not a number anymore

Seniority on the same cell

Related posts

Change the color of the weekends

Frédéric LE GUEN

Change the default Date format in Excel

Frédéric LE GUEN

How to Highlight a Birthday Automatically

Frédéric LE GUEN

Leave a Comment