 Home » Function » Date » Calculate Seniority in Excel

# Calculate Seniority in Excel

Last Updated on 16/05/2023 by Frédéric LE GUEN

Calculate seniority is a more complex operation than it seems. But Excel has a special function to calculate this 😉

## 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 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 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 of employees in Excel

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

# Calculate Seniority in Excel

Last Updated on 16/05/2023 by Frédéric LE GUEN

Calculate seniority is a more complex operation than it seems. But Excel has a special function to calculate this 😉

## 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 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 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 of employees in Excel

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