How to calculate attrition rate

[harrinho]

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

effective_date	hire_date	UNIQUE_ID	emp_id	full_name
30-04-16	29-11-10	703621Peter Jones	703621	Peter Jones
31-07-16	29-11-10	703621Peter Jones	703621	Peter Jones
31-10-16	29-11-10	703621Peter Jones	703621	Peter Jones
30-04-16	13-06-11	703778Shannon James	703778	Shannon James
31-07-16	13-06-11	703778Shannon James	703778	Shannon James
31-10-16	13-06-11	703778Shannon James	703778	Shannon James
30-04-16	14-03-11	704287John App	704287	John App
31-07-16	14-03-11	704287John App	704287	John App
31-10-16	14-03-11	704287John App	704287	John App
30-04-16	05-07-11	705704Lauren Tale	705704	Lauren Tale
31-07-16	05-07-11	705704Lauren Tale	705704	Lauren Tale
31-10-16	05-07-11	705704Lauren Tale	705704	Lauren Tale
30-04-16	06-09-11	706291Rav Deep	706291	Rav Deep
31-07-16	06-09-11	706291Rav Deep	706291	Rav Deep
31-10-16	06-09-11	706291Rav Deep	706291	Rav Deep
30-04-16	10-03-14	706560Jake Bryan	706560	Jake Bryan
30-04-16	05-12-11	707098Dina Moore	707098	Dina Moore
31-07-16	05-12-11	707098Dina Moore	707098	Dina Moore
31-10-16	05-12-11	707098Dina Moore	707098	Dina Moore
30-04-16	30-01-12	707803Shirley Jones	707803	Shirley Jones
31-07-16	30-01-12	707803Shirley Jones	707803	Shirley Jones
31-10-16	30-01-12	707803Shirley Jones	707803	Shirley Jones
30-04-16	04-09-12	708136David Juxy	708136	David Juxy
31-07-16	04-09-12	708136David Juxy	708136	David Juxy
31-10-16	04-09-12	708136David Juxy	708136	David Juxy
30-04-16	26-03-12	708364Jennifer Nolan	708364	Jennifer Nolan
31-07-16	26-03-12	708364Jennifer Nolan	708364	Jennifer Nolan

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

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.