need help with the last finishing touches

[Mathijs S]

Dear excel wizards,

I need your help again.

It is fun learning by finding things out myself. With a little help I almost finished what I was looking for. Now I’m stuck and need help with the last finishing touches.

I managed to have all cells blank when B2, D2, F2, H2, J2, L2 &L2 are empty. However, I would like to have 00:00 in B3&C3, D3&E3, F3&G3, H3&CI, J3&K3, L3&M3, N3&O3 when I type “REST” in B2, D2, F2, H2, J2, L2 &L2 and the rest of the cells empty.

Is it possible to change the background colour of the cells corresponding with the shifts when shift typed in B2, D2, F2, H2, J2, L2 &L2?

Rotation check.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	SUNDAY					MONDAY - FRIDAY					SATERDAY
2	Shift	Start	Finish	Paid Time		Shift	Start	Finish	Paid Time		Shift	Start	Finish	Paid Time
3	0WD0	00:00	08:48	07:48		0WD0	00:00	08:48	07:48		0WD0	00:00	08:48	07:48
4	0WD1	08:00	16:48	08:30		0WD2	09:00	17:48	08:30		0WD3	10:00	18:48	08:30
5	REST	00:00	00:00	00:00		REST	00:00	00:00	00:00		REST	00:00	00:00	00:00
6	4WD1	01:35	10:25	06:35		4WD1	01:35	10:25	06:35		4WD1	01:35	10:25	06:35
7	4WD2	04:35	12:35	07:15		4WD2	04:35	12:35	07:15		4WD2	04:35	12:35	07:15
8	4WD3	06:35	14:40	07:20		4WD3	04:50	13:55	08:20		4WD3	06:35	14:40	07:20
9	4WD4	08:35	19:00	08:35		4WD4	06:35	14:40	07:20		4WD4	08:35	16:40	07:20
10	4WD5	08:05	18:40	08:55		4WD5	08:35	16:40	07:20		4WD5	10:35	18:40	07:20
11	4WD6	09:45	20:40	08:45		4WD6	08:50	17:55	08:20		4WD6	12:35	20:40	07:20
12	4WD7	14:35	22:40	07:20		4WD7	10:35	18:40	07:20		4WD7	14:35	22:40	07:20
13	4WD8	17:35	01:40	07:20		4WD8	12:35	20:50	07:30		4WD8	17:35	01:35	07:15
14	4WD9	19:35	02:45	06:25		4WD9	14:35	22:40	07:20		4WD9	19:35	02:50	06:30
15	4WD10	17:35	22:45	05:10		4WD10	15:50	00:55	08:20
16
17
18
19
20
SHIFTS

Rotation check.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		SUNDAY		Monday		Tuesday		Wednesday		Thursday		Friday		SATERDAY		WORK
2	1	REST				4wd2		4wd3		4wd4		4wd5		REST		S PT	ø PT
3		00:00	00:00			04:35	12:35	04:50	13:55	06:35	14:40	08:35	16:40	00:00	00:00	30:15	7:33
4		Total	00:00			Total	08:00	Total	09:05	Total	08:05	Total	08:05	Total	00:00
5		Break	00:00			Break	00:45	Break	00:45	Break	00:45	Break	00:45	Break	00:00
6		Paid Time	00:00			Paid Time	07:15	Paid Time	08:20	Paid Time	07:20	Paid Time	07:20	Paid Time	00:00
Rotation

Cell Formulas
Range	Formula
B3	B3	=IFERROR(INDEX(SHIFTS!B$3:B$1048576,MATCH(B2,SHIFTS!A$3:A$1048576,0)),"")
C3	C3	=IFERROR(INDEX(SHIFTS!C$3:C$1048576,MATCH(B2,SHIFTS!A$3:A$1048576,0)),"")
D3	D3	=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(D2,SHIFTS!F$3:F$1048576,0)),"")
E3	E3	=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(D2,SHIFTS!F$3:F$1048576,0)),"")
F3	F3	=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(F2,SHIFTS!F$3:F$1048576,0)),"")
G3	G3	=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(F2,SHIFTS!F$3:F$1048576,0)),"")
H3	H3	=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(H2,SHIFTS!F$3:F$1048576,0)),"")
I3	I3	=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(H2,SHIFTS!F$3:F$1048576,0)),"")
J3	J3	=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(J2,SHIFTS!F$3:F$1048576,0)),"")
K3	K3	=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(J2,SHIFTS!F$3:F$1048576,0)),"")
L3	L3	=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(L2,SHIFTS!F$3:F$1048576,0)),"")
M3	M3	=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(L2,SHIFTS!F$3:F$1048576,0)),"")
N3	N3	=IFERROR(INDEX(SHIFTS!L$3:L$1048576,MATCH(N2,SHIFTS!K$3:K$1048576,0)),"")
O3	O3	=IFERROR(INDEX(SHIFTS!M$3:M$1048576,MATCH(N2,SHIFTS!K$3:K$1048576,0)),"")
P3	P3	=SUM(C6,E6,G6,I6,K6,M6,O7)
Q3	Q3	=IFERROR(P3/COUNTIF(B2:O2,"*wd*"),"00:00")
B4,D4,F4,H4,J4,L4,N4	B4	=IF(B2="","","Total")
C4,E4,G4,I4,K4,M4,O4	C4	=IFERROR(C3-B3,"")
B5,D5,F5,H5,J5,L5,N5	B5	=IF(B2="","","Break")
C5,E5,G5,I5,K5,M5,O5	C5	=IFERROR(C4-C6,"")
B6,D6,F6,H6,J6,L6,N6	B6	=IF(B2="","","Paid Time")
C6	C6	=IFERROR(INDEX(SHIFTS!D$3:D$1048576,MATCH(B2,SHIFTS!A$3:A$1048576,0)),"")
E6	E6	=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(D2,SHIFTS!F$3:F$1048576,0)),"")
G6	G6	=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(F2,SHIFTS!F$3:F$1048576,0)),"")
I6	I6	=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(H2,SHIFTS!F$3:F$1048576,0)),"")
K6	K6	=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(J2,SHIFTS!F$3:F$1048576,0)),"")
M6	M6	=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(L2,SHIFTS!F$3:F$1048576,0)),"")
O6	O6	=IFERROR(INDEX(SHIFTS!N$3:N$1048576,MATCH(N2,SHIFTS!K$3:K$1048576,0)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:M2	Cell Value	contains "REST"	text	NO
J2:K2	Cell Value	contains "REST"	text	NO
H2:I2	Cell Value	contains "REST"	text	NO
F2:G2	Cell Value	contains "REST"	text	NO
B2:C2	Cell Value	contains "REST"	text	NO
C5	Cell Value	between 0.000694444444444444 and 0.0305555555555556	text	NO
N2:O2	Cell Value	contains "REST"	text	NO
D2:E2	Cell Value	contains "REST"	text	NO
O5	Cell Value	between 0.000694444444444444 and 0.0305555555555556	text	NO
M5	Cell Value	between 0.000694444444444444 and 0.0305555555555556	text	NO
K5	Cell Value	between 0.000694444444444444 and 0.0305555555555556	text	NO
I5	Cell Value	between 0.000694444444444444 and 0.0305555555555556	text	NO
G5	Cell Value	between 0.000694444444444444 and 0.0305555555555556	text	NO
E5	Cell Value	between 0.000694444444444444 and 0.0305555555555556	text	NO

 

[padapinto]

is this a timecard you are creating, and if so, is it monthly and also is it for multiple employees or for only one? i have a super one that could make your work even easier.

 

[Mathijs S]

is this a timecard you are creating, and if so, is it monthly and also is it for multiple employees or for only one? i have a super one that could make your work even easier.

Hi padapinto,

Thanks for your reply.
It is to make an existing rostering system, for a group of workers, a little simpler. It is the first line of a rotation and by copying I can make 2, 4, 6 or more week rotations and can check weekly work and rest hours.

 

[padapinto]

Hi padapinto,

Thanks for your reply.
It is to make an existing rostering system, for a group of workers, a little simpler. It is the first line of a rotation and by copying I can make 2, 4, 6 or more week rotations and can check weekly work and rest hours.

what I have is for making monthly timecard but you can create copies of it in the same sheet for every employee. say if you have 6 employees you will have 6 sheets and with that timecard, you already have 4 weeks schedule if your option is just to record how many hrs. they work and how many hrs. they rest.
SINGLE TIMECARD FOR DRIVE.xlsx.
Do reply back if this helps you

 

[Mathijs S]

what I have is for making monthly timecard but you can create copies of it in the same sheet for every employee. say if you have 6 employees you will have 6 sheets and with that timecard, you already have 4 weeks schedule if your option is just to record how many hrs. they work and how many hrs. they rest.
SINGLE TIMECARD FOR DRIVE.xlsx.
Do reply back if this helps you

Hi padapinto,

It doesn’t help me with making my roster, but I downloaded it and I definitely will (if it is okay with you) come back to you when I would like to (partially) use it or make it work for me.

Are you, by any chance, able to help me out with the 2 things I’m stuck with?

 

[padapinto]

Hi padapinto,

It doesn’t help me with making my roster, but I downloaded it and I definitely will (if it is okay with you) come back to you when I would like to (partially) use it or make it work for me.

Are you, by any chance, able to help me out with the 2 things I’m stuck with?

i havent been online for quite some days but here i am now. the reason i dint tackle your query at thefirst time was i dint understand when or what should happen for yo to fill in 00:00 hrs. mind elaborating, without referencing so i can get a gesture of what is it thats needed.

 

[Mathijs S]

I managed to have all cells blank (tab "Rotation") when B2, D2, F2, H2, J2, L2 &L2 are empty. However, I would like to have 00:00 in B3&C3, D3&E3, F3&G3, H3&CI, J3&K3, L3&M3, N3&O3 when I type “REST” in B2, D2, F2, H2, J2, L2 &L2 and the rest of the cells empty.

Is it possible to change the background colour of the cells corresponding with the shifts when shift typed in B2, D2, F2, H2, J2, L2 &L2?