Counting instances of sickness excluding the weekend

[KV1993]

Hi,

I would like to seek help on how I count sick leave instances.

My data are

Column A - Employee Number
Column B - Employee Name
Column C - Sick Leave Date (Newest to Oldest)
Colum D - is where I should put the formula to calculate the instances.

If member is sick leave on Friday until Monday next week it should only be counted as one instance. We have existing formula in our old reports, however, it is counting next week as new instances.

Appreciate any help. Thank you so much.

 

[awoohaw]

please post some sample data using the xl2bb add in (link below).
at minimum a TABLE (not screen shot) of your data, and expected results.

 

[KV1993]

Hi, Thank you so much for your reply. I tried the XL2BB and I can't manage to make it work. I'm so sorry. But, this is the sample data. I can't add the original data sorry.

EE IDN	Name	Sick Date	Absence Instances
54321	John	20/07/2011	1
54321	John	03/10/2011	2
54321	John	18/10/2011	2
54321	John	19/10/2011	2
54321	John	20/10/2011	2
54321	John	21/10/2011	2
67894	Emma	21/07/2011	1
67894	Emma	22/07/2011	1
67894	Emma	25/07/2011	1
67894	Emma	26/07/2011	1
67894	Emma	27/07/2011	1
67894	Emma	28/07/2011	1
67894	Emma	29/07/2011	1
55448	Betty	15/08/2011	1
55448	Betty	16/08/2011	1
55448	Betty	29/08/2011	2
99544	Carl	04/10/2011	1
99544	Carl	05/10/2011	1
99544	Carl	06/12/2011	2
99544	Carl	12/01/2012	3
99544	Carl	13/01/2012	3
99544	Carl	16/01/2012	3
99544	Carl	17/01/2012	3
99544	Carl	18/01/2012	3
33567	Andrew	19/03/2012	1
33567	Andrew	22/03/2012	1
33567	Andrew	23/03/2012	1

I need to count that John has 2 instances of sick leave, Emma - 1, Betty - 2, Carl - 3 and Andrew - 1.
Thank you so much for the help. I sincerely appreciate it.

 

[Fluff]

Not sure how you got those numbers, but how about

Fluff.xlsm
	A	B	C	D
1	EE IDN	Name	Sick Date	Absence Instances
2	54321	John	20/07/2011	1
3	54321	John	03/10/2011	2
4	54321	John	18/10/2011	3
5	54321	John	19/10/2011	3
6	54321	John	20/10/2011	3
7	54321	John	21/10/2011	3
8	67894	Emma	21/07/2011	1
9	67894	Emma	22/07/2011	1
10	67894	Emma	25/07/2011	1
11	67894	Emma	26/07/2011	1
12	67894	Emma	27/07/2011	1
13	67894	Emma	28/07/2011	1
14	67894	Emma	29/07/2011	1
15	55448	Betty	15/08/2011	1
16	55448	Betty	16/08/2011	1
17	55448	Betty	29/08/2011	2
18	99544	Carl	04/10/2011	1
19	99544	Carl	05/10/2011	1
20	99544	Carl	06/12/2011	2
21	99544	Carl	12/01/2012	3
22	99544	Carl	13/01/2012	3
23	99544	Carl	16/01/2012	3
24	99544	Carl	17/01/2012	3
25	99544	Carl	18/01/2012	3
26	33567	Andrew	19/03/2012	1
27	33567	Andrew	22/03/2012	2
28	33567	Andrew	23/03/2012	2
Sheet4

Cell Formulas
Range	Formula
D2:D28	D2	=IF(A2<>A1,1,IF(NETWORKDAYS(C1,C2)=2,D1,D1+1))

 

[KV1993]

Hi Fluff,

Thank you so much for your help! It works! I just manually counted the instances in the sample data. Haha

-KV

 

[Fluff]

You're welcome & thanks for the feedback.