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?
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 |