Employee shift counter

[liampog]

Hi

I am trying, with limited success, so set up a counter that counts whether an employee is present on shift or not, which would output as shown in the image below.

It needs to correctly handle shifts finishing at midnight (3rd shift example) and shifts that bridge midnight (4th shift example).

It also needs to be able to handle shifts that finish past the 06:00 end time of the table (5th example).

I am going to extrapolate any solutions into a 7 day per week table that counts each day.

Can anyone assist with this?

Thanks

 

[PeteWright]

Hi @liampog

From your post I don't see what / where the data is and what / where formulas should be.

Could you please tell me, which cells should do some calculations and which cells contain data?

Regards
Pete

 

[liampog]

Sorry, you're right it's a bit vague.

The cells with 0 and 1 would need to be formulas. The cells number 07 to 06 are times (formatted as such). The cells on the left are user input shift times (formatted as text) in 24 hour clock format.

They look at the shift entered in the left most column and determine (based upon the times at the top from 07:00 to 06:00) whether the shift time on the right means an employee is present at the time at the top.

So if you see, an employee on a 13:00 - 21:00 shift is present (shown by number 1) from 13:00 to 20:00, and then is not present from 21:00 onwards (shown by number 0).

I hope this is clearer than my original post.

 

[PeteWright]

Hi @liampog
I may have figured it out...

Please paste the following formula into cell [C2]:

=IF(ISBLANK($B2),"",IF(NOT(ISNA(XMATCH(C$1*24,IF(IF(AND(NUMBERVALUE(MID($B2,LEN($B2)-4,2))>NUMBERVALUE(LEFT($B2,2)),NUMBERVALUE(LEFT($B2,2))>=0,NUMBERVALUE(LEFT($B2,2))<23,NUMBERVALUE(MID($B2,LEN($B2)-4,2))>0,NUMBERVALUE(MID($B2,LEN($B2)-4,2))<=23),NUMBERVALUE(MID($B2,LEN($B2)-4,2))-NUMBERVALUE(LEFT($B2,2)),0)<>0,SEQUENCE(1,IF(AND(NUMBERVALUE(MID($B2,LEN($B2)-4,2))>NUMBERVALUE(LEFT($B2,2)),NUMBERVALUE(LEFT($B2,2))>=0,NUMBERVALUE(LEFT($B2,2))<23,NUMBERVALUE(MID($B2,LEN($B2)-4,2))>0,NUMBERVALUE(MID($B2,LEN($B2)-4,2))<=23),NUMBERVALUE(MID($B2,LEN($B2)-4,2))-NUMBERVALUE(LEFT($B2,2)),0),NUMBERVALUE(LEFT($B2,2)),1),IF(NUMBERVALUE(MID($B2,LEN($B2)-4,2))=0,SEQUENCE(1,IF(IF(AND(NUMBERVALUE(MID($B2,LEN($B2)-4,2))>NUMBERVALUE(LEFT($B2,2)),NUMBERVALUE(LEFT($B2,2))>=0,NUMBERVALUE(LEFT($B2,2))<23,NUMBERVALUE(MID($B2,LEN($B2)-4,2))>0,NUMBERVALUE(MID($B2,LEN($B2)-4,2))<=23),NUMBERVALUE(MID($B2,LEN($B2)-4,2))-NUMBERVALUE(LEFT($B2,2)),0)=0,24-NUMBERVALUE(LEFT($B2,2)),0),NUMBERVALUE(LEFT($B2,2)),1),HSTACK(SEQUENCE(1,IF(IF(AND(NUMBERVALUE(MID($B2,LEN($B2)-4,2))>NUMBERVALUE(LEFT($B2,2)),NUMBERVALUE(LEFT($B2,2))>=0,NUMBERVALUE(LEFT($B2,2))<23,NUMBERVALUE(MID($B2,LEN($B2)-4,2))>0,NUMBERVALUE(MID($B2,LEN($B2)-4,2))<=23),NUMBERVALUE(MID($B2,LEN($B2)-4,2))-NUMBERVALUE(LEFT($B2,2)),0)=0,24-NUMBERVALUE(LEFT($B2,2)),0),NUMBERVALUE(LEFT($B2,2)),1),SEQUENCE(1,IF(IF(AND(NUMBERVALUE(MID($B2,LEN($B2)-4,2))>NUMBERVALUE(LEFT($B2,2)),NUMBERVALUE(LEFT($B2,2))>=0,NUMBERVALUE(LEFT($B2,2))<23,NUMBERVALUE(MID($B2,LEN($B2)-4,2))>0,NUMBERVALUE(MID($B2,LEN($B2)-4,2))<=23),NUMBERVALUE(MID($B2,LEN($B2)-4,2))-NUMBERVALUE(LEFT($B2,2)),0)=0,NUMBERVALUE(MID($B2,LEN($B2)-4,2)),0),0,1)))),0,1))),1,0))

The formula is huge, because I've made it up from a dozen helper cells.

Please let me know if it works and if you're OK with this long formula.

 

[awoohaw]

I did this a little different than your original question, I'm not sure it will fit your requirement. Also, I had to size restrictions on the post, but, I hope the spilled array formulas will spill down. You'll just need to drag the formula in B2 over and down to complete the grid.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1		0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	0	1	2	3	4	5	6
2	0:00 - 8:00	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	1:00 - 9:00	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	2:00 - 10:00	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	3:00 - 11:00	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	4:00 - 12:00	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	5:00 - 13:00	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
8	6:00 - 14:00	0	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9	7:00 - 15:00	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	8:00 - 16:00	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	9:00 - 17:00	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	10:00 - 18:00	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0
13	11:00 - 19:00	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
B1:AF1	B1	=0/24+SEQUENCE(1,31,0,1)/24
A2:A33	A2	=LET(s,TEXT(SEQUENCE(32,1,0,1)/24,"h:mm"),e,TEXT(s+8/24,"h:mm"),s & " - " & e)
B2:AF13	B2	=LET(s,TIMEVALUE(TEXTBEFORE($A2," ")),ie,TIMEVALUE(TEXTAFTER($A2," ",2)), e,IF(ie<s,ie+1,ie), h,B$1,--AND(h>=s,h<e))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AF33	Cell Value	=1	text	NO