How to calculate attrition rate

[harrinho]

I want to calculate the attrition rate in the following data set

[TABLE="width: 503"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]effective_date[/TD]
[TD]hire_date[/TD]
[TD]UNIQUE_ID[/TD]
[TD]emp_id[/TD]
[TD]full_name[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]29-11-10[/TD]
[TD]703621Peter Jones[/TD]
[TD="align: right"]703621[/TD]
[TD]Peter Jones[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]29-11-10[/TD]
[TD]703621Peter Jones[/TD]
[TD="align: right"]703621[/TD]
[TD]Peter Jones[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16[/TD]
[TD="align: right"]29-11-10[/TD]
[TD]703621Peter Jones[/TD]
[TD="align: right"]703621[/TD]
[TD]Peter Jones[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]13-06-11[/TD]
[TD]703778Shannon James[/TD]
[TD="align: right"]703778[/TD]
[TD]Shannon James[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]13-06-11[/TD]
[TD]703778Shannon James[/TD]
[TD="align: right"]703778[/TD]
[TD]Shannon James[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16[/TD]
[TD="align: right"]13-06-11[/TD]
[TD]703778Shannon James[/TD]
[TD="align: right"]703778[/TD]
[TD]Shannon James[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]14-03-11[/TD]
[TD]704287John App[/TD]
[TD="align: right"]704287[/TD]
[TD]John App[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]14-03-11[/TD]
[TD]704287John App[/TD]
[TD="align: right"]704287[/TD]
[TD]John App[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16[/TD]
[TD="align: right"]14-03-11[/TD]
[TD]704287John App[/TD]
[TD="align: right"]704287[/TD]
[TD]John App[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]05-07-11[/TD]
[TD]705704Lauren Tale[/TD]
[TD="align: right"]705704[/TD]
[TD]Lauren Tale[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]05-07-11[/TD]
[TD]705704Lauren Tale[/TD]
[TD="align: right"]705704[/TD]
[TD]Lauren Tale[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16[/TD]
[TD="align: right"]05-07-11[/TD]
[TD]705704Lauren Tale[/TD]
[TD="align: right"]705704[/TD]
[TD]Lauren Tale[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]06-09-11[/TD]
[TD]706291Rav Deep[/TD]
[TD="align: right"]706291[/TD]
[TD]Rav Deep[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]06-09-11[/TD]
[TD]706291Rav Deep[/TD]
[TD="align: right"]706291[/TD]
[TD]Rav Deep[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16[/TD]
[TD="align: right"]06-09-11[/TD]
[TD]706291Rav Deep[/TD]
[TD="align: right"]706291[/TD]
[TD]Rav Deep[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]10-03-14[/TD]
[TD]706560Jake Bryan [/TD]
[TD="align: right"]706560[/TD]
[TD]Jake Bryan [/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]05-12-11[/TD]
[TD]707098Dina Moore[/TD]
[TD="align: right"]707098[/TD]
[TD]Dina Moore[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]05-12-11[/TD]
[TD]707098Dina Moore[/TD]
[TD="align: right"]707098[/TD]
[TD]Dina Moore[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16[/TD]
[TD="align: right"]05-12-11[/TD]
[TD]707098Dina Moore[/TD]
[TD="align: right"]707098[/TD]
[TD]Dina Moore[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]30-01-12[/TD]
[TD]707803Shirley Jones[/TD]
[TD="align: right"]707803[/TD]
[TD]Shirley Jones[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]30-01-12[/TD]
[TD]707803Shirley Jones[/TD]
[TD="align: right"]707803[/TD]
[TD]Shirley Jones[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16[/TD]
[TD="align: right"]30-01-12[/TD]
[TD]707803Shirley Jones[/TD]
[TD="align: right"]707803[/TD]
[TD]Shirley Jones[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]04-09-12[/TD]
[TD]708136David Juxy[/TD]
[TD="align: right"]708136[/TD]
[TD]David Juxy[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]04-09-12[/TD]
[TD]708136David Juxy[/TD]
[TD="align: right"]708136[/TD]
[TD]David Juxy[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16[/TD]
[TD="align: right"]04-09-12[/TD]
[TD]708136David Juxy[/TD]
[TD="align: right"]708136[/TD]
[TD]David Juxy[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16[/TD]
[TD="align: right"]26-03-12[/TD]
[TD]708364Jennifer Nolan [/TD]
[TD="align: right"]708364[/TD]
[TD]Jennifer Nolan [/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16[/TD]
[TD="align: right"]26-03-12[/TD]
[TD]708364Jennifer Nolan [/TD]
[TD="align: right"]708364[/TD]
[TD]Jennifer Nolan [/TD]
[/TR]
</tbody>[/TABLE]
As you can see the patter is that there is a record every 3 months, so when an employee is not recorded in the next record (effective date) it means he/she has left the company. So, in my opinion I'd need a new column that would flag or count those employees. For example, the red highlighted employees should be counted to those who left since they are not recorded in next months record.

Can that be done without a VB macro?

 

[Tetra201]

For this particular dataset, put the following formula in cell F2 and drag-copy down. The formula returns "Departed" for the employees who have left the company.

=IF(MOD(COUNTIF($D$2:$D$28,$D2),3),"Departed","")

 

[harrinho]

For this particular dataset, put the following formula in cell F2 and drag-copy down. The formula returns "Departed" for the employees who have left the company.

=IF(MOD(COUNTIF($D$2:$D$28,$D2),3),"Departed","")

Man you are a genius. What I don't get is what is the criteria in MOD, I mean shouldn't be like:
=IF(MOD(COUNTIF($D$2:$D$28,$D2),3<1),"Departed","")?

 

[Tetra201]

The formula from Post #2 is functionally identical to:

=IF(MOD(COUNTIF($D$2:$D$28,$D2),3)<>0,"Departed","")

It works the same way with the <>0 and without it.

 

[harrinho]

The formula from Post #2 is functionally identical to:

=IF(MOD(COUNTIF($D$2:$D$28,$D2),3)<>0,"Departed","")

It works the same way with the <>0 and without it.

Much appreciated, thank you!

 

[Tetra201]

You are welcome.