Count consecutive sick leave

[hainer76]

Hope you all are doing well

I have managed to put together a sick leave tracker in excel. (Might hopefully in part or as a whole be useful to someone.)
Some time ago I found two methods for counting consecutive cells with the same value in a row.
Unfortunately I do not remember the original poster/s but all credit where it's due. I have modified the formulas to check for 4 and 5 consecutive counts as well in (Method1) and the same for the array option in (Method2).

The worksheet:

• a seperate sheet for each employee covering 52 weeks (row 2 to row 53)
• public holidays do not get counted (added these in a named range)
• each week (one row) divided up into days of the week (column E to I)

Method1:
ex. The formulas in cells K2 to P2
"s" is entered into a cell when the day is taken as sick leave (column E to I)
K2 - helper column with "text string"
=IF(E2=""," ","s")&IF(F2=""," ","s")&IF(G2=""," ","s")&IF(H2=""," ","s")&IF(I2=""," ","s")
L2 - checking fr single days
=(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss",""),"ss",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss",""),"ss",""),"s","")))
M2 - checking for 2 consecutive days
=(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss",""),"ss","")))/2
N2 - checking for 3 consecutive days=(LEN(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss","")))/3
O2 - checking for 4 consecutive days
=(LEN(SUBSTITUTE(O2,"sssss",""))-LEN(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss","")))/4
P2 - checking for 5 consecutive days
=IF(COUNTIF(E2:I2,"")<1,1,"")
Method2:

same basic setup as in Method1 but using array formulas
- checking for 2 consecutive days
{=SUM(IF(FREQUENCY(IF(E2:I2="s",COLUMN(E2:I2)),IF(E2:I2<>"s",COLUMN(E2:I2)))=2,1))}

- checking for 3 consecutive days
{=SUM(IF(FREQUENCY(IF(E2:I2="s",COLUMN(E2:I2)),IF(E2:I2<>"s",COLUMN(E2:I2)))=3,1))}

- checking for 4 consecutive days
{=SUM(IF(FREQUENCY(IF(E2:I2="s",COLUMN(E2:I2)),IF(E2:I2<>"s",COLUMN(E2:I2)))=4,1))}

- checking for 5 consecutive days
{=SUM(IF(FREQUENCY(IF(E2:I2="s",COLUMN(E2:I2)),IF(E2:I2<>"s",COLUMN(E2:I2)))=5,1))}

This all works great. The problem I now face is the following. A working example, ex. employee named Pete.

Pete has taken Monday off sick, the Friday off sick in the same week and the Monday and Tuesday in the following week.
In this example the first row(Mon/Fri) formulas would evaluate to two single days of sick leave and the second row(Mon/Tue) to two consecutive days.
What I would like to happen is the first row to evaluate to one day (Mon) and the second row to evaluate to three consecutive days (Fri/Mon/Tue).

So what I would really appreciate help with is the following:

• if a Friday is taken as sick leave check if it is part of consecutive days of leave, i.e. (Thu/Fri or Wed/Thu/Fri)
• check if the following Monday is taken as sick leave
• if one or both of the criteria is met add the Friday to the following week
• any non-consecutive days are recorded in the relevant rows

Mon	Tue	Wed	Thu	Fri	Single	Consecutive	Helper
s			s	s	1		Yes
s			s		1	3	No
s	s		s		1	2

<tbody>
</tbody>

Any help/suggestions would be greatly appreciated.

Kind Regards

 

[DRSteele]

Post an example using Mr Excel HTML maker, here: http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

 

[hainer76]

DRSteele

Many thanks for the reply.
I have never used HTML maker before.
I have Excel 2010 and 2013.
The add-in is activated in both, the menu options are available on right-click and in the drop down menu on the ribbon.

I have tried the default and all formulas options.

On pasting the selection into the editor I eventually get the small "Kill Pages / Wait" pop-up window.
If I select wait the "editor", this window, is filled with what appears to be web page code.
Does that mean the selection was successfully loaded?

Are you open to any other methods?
Windows SkyDrive or direct email?
I do apologise for any inconvenience caused.
Any help would be greatly appreciated.
Kind Regards

 

[DRSteele]

Yes, that web-codey-lookin stuff is alright --- it's then loaded. Press Go Advanced and Preview post and you'll see what your post will look like. Then when it's OK press Submit Reply.

 

[MARK858]

Edit: Too slow was same response as post #4.....Deleted

 

[hainer76]

Many thanks DRSteele and MARK858. Worked in Excel 2010 selecting "Default".
Please note the sheet includes both the methods I found on this site.
I am open to any suggestions to which might be better avoiding VBA regardless.

Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Week	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	2 consecutive Sick Days	3 consecutive Sick Days	4 consecutive Sick Days	5 consecutive Sick Days		1 Day Sick	2 Days Sick	3 Days Sick	4 Days Sick	One Week
2	1			s			s	s	1	0	0	0	s ss	1	1	0	0
3	2				s	s	s		0	1	0	0	sss	0	0	1	0
4	3							s	0	0	0	0	s	1	0	0	0
5	4			s	s		s	s	2	0	0	0	ss ss	0	2	0	0
6	5							s	0	0	0	0	s	1	0	0	0
7	6				s	s			1	0	0	0	ss	0	1	0	0
8	7			s		s		s	0	0	0	0	s s s	3	0	0	0
9	8			s		s	s		1	0	0	0	s ss	1	1	0	0
10	9			s	s	s	s	s	0	0	0	1	sssss	0	0	0	0	1
11	10			s		s			0	0	0	0	s s	2	0	0	0
12	11								0	0	0	0		0	0	0	0
13	12								0	0	0	0		0	0	0	0
14	13								0	0	0	0		0	0	0	0
15	14								0	0	0	0		0	0	0	0

<tbody>
</tbody>

Sheet2​

Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
17	Formulas in J2 to M15	=SUM(IF(FREQUENCY(IF(E2:I2="s",COLUMN(E2:I2)),IF(E2:I2<>"s",COLUMN(E2:I2)))=2,1)) =SUM(IF(FREQUENCY(IF(E2:I2="s",COLUMN(E2:I2)),IF(E2:I2<>"s",COLUMN(E2:I2)))=3,1)) =SUM(IF(FREQUENCY(IF(E2:I2="s",COLUMN(E2:I2)),IF(E2:I2<>"s",COLUMN(E2:I2)))=4,1))
18	Formulas in N2 to N15	=IF(E2=""," ","s")&IF(F2=""," ","s")&IF(G2=""," ","s")&IF(H2=""," ","s")&IF(I2=""," ","s")
19	Formulas in O2 to O15	=(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss",""),"ss",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss",""),"ss",""),"s","")))
20	Formulas in P2 to P15	=(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss",""),"ss","")))/2
21	Formulas in Q2 to Q15	=(LEN(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss",""),"sss","")))/3
22	Formulas in R2 to R15	=(LEN(SUBSTITUTE(O2,"sssss",""))-LEN(SUBSTITUTE(SUBSTITUTE(O2,"sssss",""),"ssss","")))/4
23	Formulas in S2 to S15	=IF(COUNTIF(E2:I2,"")<1,1,"")

<tbody>
</tbody>

Sheet2​