Calculate Seniority in Excel

Calculate Seniority in Excel
Last Updated on 29/09/2023
Reading time: 2 minutes

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.

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 the number of days
  • m returns the difference in the number of months
  • y returns the difference in the 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 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.

Calculate seniority with hiring dates

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

Seniority on the same cell

Leave a Reply

Your email address will not be published. Required fields are marked *

Calculate Seniority in Excel

Reading time: 2 minutes
Last Updated on 29/09/2023

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.

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 the number of days
  • m returns the difference in the number of months
  • y returns the difference in the 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 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.

Calculate seniority with hiring dates

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

Seniority on the same cell

Leave a Reply

Your email address will not be published. Required fields are marked *