Retention Rate and Tenure Rate for employees for current year

Lizneth

I am needing help. I want to create a dashboard to see the current years tenure rate and retention rate by tracking month to month.

I have a column for retention status (active vs terminated rate), active status count. Termination month, termination year, termination date, hire date.

I have a formula for total employee count, terminated employee count and current employee count for the year.

I need a formula to help me calculate retention rate and tenure rate please. If I have to add more columns I will do so. If you have created a sheet with this information please help me out. Thank you in advance!

Genevieve P.

Hi @Lizneth

For retention rate, this is how I would calculate it:

• Divide the total number of employees who stayed with your company through the time period by the headcount you started with on day one. 

What I would do is have one column that lists out the Month you're looking for, and another one that has this formula next to it. This way you can select both columns for your Chart and have it display on a Dashboard month-over-month.

Now, for the calculation, you'll want to figure out how many employees were active for that month. Then you can subtract the number of employees terminated in that month from it, and divide it by the original number of active employees.

(Active Employees - Terminated this month) / Active Employees

This is the criteria to get the Active Employees:

• Hired before the end of the month
• NOT hired after the end of the month 
• NOT terminated before the beginning of the month
• CAN be terminated after the end of the month 
• CAN have a blank terminated cell, noting they're currently active

I would use a COUNTIFS For this, specifying the MONTH by using the DATE function.

So for January, 2023:

=COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 1, 31), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 1, 1)))

This counts how many rows have a Hire Date prior or equal to Jan 31st, but a termination date that either doesn't exist (is blank) or is within this month (greater than or equal to Jan 1st) or in the future.

Then you can subtract from this how many were terminated this month:

- COUNTIFS([Termination Date]:[Termination Date], >=DATE(2023, 1, 1), [Termination Date]:[Termination Date], <=DATE(2023, 1, 31))

And divide these two formulas by the original Count:

=(COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 1, 31), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 1, 1))) - COUNTIFS([Termination Date]:[Termination Date], >=DATE(2023, 1, 1), [Termination Date]:[Termination Date], <=DATE(2023, 1, 31))) / COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 1, 31), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 1, 1)))

That will give you a decimal output that you can use to format the column as a % type and see a percentage for the month.

For February you'll need to change out the date ranges:

=(COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 2, 28), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 2, 1))) - COUNTIFS([Termination Date]:[Termination Date], >=DATE(2023, 2, 1), [Termination Date]:[Termination Date], <=DATE(2023, 2, 28))) / COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 2, 28), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 2, 1)))

And so on for each month. Does that make sense and will that work for you?

Genevieve P.

Hi @Lizneth

For retention rate, this is how I would calculate it:

• Divide the total number of employees who stayed with your company through the time period by the headcount you started with on day one. 

What I would do is have one column that lists out the Month you're looking for, and another one that has this formula next to it. This way you can select both columns for your Chart and have it display on a Dashboard month-over-month.

Now, for the calculation, you'll want to figure out how many employees were active for that month. Then you can subtract the number of employees terminated in that month from it, and divide it by the original number of active employees.

(Active Employees - Terminated this month) / Active Employees

This is the criteria to get the Active Employees:

• Hired before the end of the month
• NOT hired after the end of the month 
• NOT terminated before the beginning of the month
• CAN be terminated after the end of the month 
• CAN have a blank terminated cell, noting they're currently active

I would use a COUNTIFS For this, specifying the MONTH by using the DATE function.

So for January, 2023:

=COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 1, 31), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 1, 1)))

This counts how many rows have a Hire Date prior or equal to Jan 31st, but a termination date that either doesn't exist (is blank) or is within this month (greater than or equal to Jan 1st) or in the future.

Then you can subtract from this how many were terminated this month:

- COUNTIFS([Termination Date]:[Termination Date], >=DATE(2023, 1, 1), [Termination Date]:[Termination Date], <=DATE(2023, 1, 31))

And divide these two formulas by the original Count:

=(COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 1, 31), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 1, 1))) - COUNTIFS([Termination Date]:[Termination Date], >=DATE(2023, 1, 1), [Termination Date]:[Termination Date], <=DATE(2023, 1, 31))) / COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 1, 31), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 1, 1)))

That will give you a decimal output that you can use to format the column as a % type and see a percentage for the month.

For February you'll need to change out the date ranges:

=(COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 2, 28), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 2, 1))) - COUNTIFS([Termination Date]:[Termination Date], >=DATE(2023, 2, 1), [Termination Date]:[Termination Date], <=DATE(2023, 2, 28))) / COUNTIFS([Hire Date]:[Hire Date], <=DATE(2023, 2, 28), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2023, 2, 1)))

And so on for each month. Does that make sense and will that work for you?

Genevieve P.

In regards to Tenure rate, can you clarify exactly how you want this calculated?

The easiest thing to do would be to have one column set up that identifies the days worked for each person:

=IF([Termination Date]@row = "", TODAY(), [Termination Date]@row) - [Hire Date]@row

Then you can simply average this column for the total average tenure in Days across all past and present employees.

Cheers,

Genevieve

Lizneth

@Genevieve P. Thank you so much for the time you put into explaining each detail to me. I was able to plug in the formula and no need to adjust anything for month to month retention rate. I put Tenure but I meant turnover rate instead. Sorry about the confusion.

Can you tell me how you would calculate retention for the year instead of month to month please? So prior to january 31st?

Genevieve P.

Hey @Lizneth

I'm glad to hear this was helpful for you!

You can adjust the formula to look at any date range you wanted... so if you wanted to see everything only for the year 2022, then change the dates in the formula like so:

=(COUNTIFS([Hire Date]:[Hire Date], <=DATE(2022, 12, 31), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2022, 1, 1))) - COUNTIFS([Termination Date]:[Termination Date], >=DATE(2022, 1, 1), [Termination Date]:[Termination Date], <=DATE(2022, 12, 31))) / COUNTIFS([Hire Date]:[Hire Date], <=DATE(2022, 12, 31), [Termination Date]:[Termination Date], OR(@cell = "", @cell >= DATE(2022, 1, 1)))

This has the "start" of the range set to January 1st, 2022, and the "end" of the range set to December 31st, 2022

Does that work for you?

Cheers,

Genevieve