need help with the last finishing touches

Mathijs S

New Member
Joined
Oct 14, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
1SUNDAYMONDAY - FRIDAYSATERDAY
2ShiftStartFinishPaid TimeShiftStartFinishPaid TimeShiftStartFinishPaid Time
30WD000:0008:4807:480WD000:0008:4807:480WD000:0008:4807:48
40WD108:0016:4808:300WD209:0017:4808:300WD310:0018:4808:30
5REST00:0000:0000:00REST00:0000:0000:00REST00:0000:0000:00
64WD101:3510:2506:354WD101:3510:2506:354WD101:3510:2506:35
74WD204:3512:3507:154WD204:3512:3507:154WD204:3512:3507:15
84WD306:3514:4007:204WD304:5013:5508:204WD306:3514:4007:20
94WD408:3519:0008:354WD406:3514:4007:204WD408:3516:4007:20
104WD508:0518:4008:554WD508:3516:4007:204WD510:3518:4007:20
114WD609:4520:4008:454WD608:5017:5508:204WD612:3520:4007:20
124WD714:3522:4007:204WD710:3518:4007:204WD714:3522:4007:20
134WD817:3501:4007:204WD812:3520:5007:304WD817:3501:3507:15
144WD919:3502:4506:254WD914:3522:4007:204WD919:3502:5006:30
154WD1017:3522:4505:104WD1015:5000:5508:20
16
17
18
19
20
SHIFTS



Cell Formulas
RangeFormula
B3B3=IFERROR(INDEX(SHIFTS!B$3:B$1048576,MATCH(B2,SHIFTS!A$3:A$1048576,0)),"")
C3C3=IFERROR(INDEX(SHIFTS!C$3:C$1048576,MATCH(B2,SHIFTS!A$3:A$1048576,0)),"")
D3D3=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(D2,SHIFTS!F$3:F$1048576,0)),"")
E3E3=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(D2,SHIFTS!F$3:F$1048576,0)),"")
F3F3=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(F2,SHIFTS!F$3:F$1048576,0)),"")
G3G3=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(F2,SHIFTS!F$3:F$1048576,0)),"")
H3H3=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(H2,SHIFTS!F$3:F$1048576,0)),"")
I3I3=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(H2,SHIFTS!F$3:F$1048576,0)),"")
J3J3=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(J2,SHIFTS!F$3:F$1048576,0)),"")
K3K3=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(J2,SHIFTS!F$3:F$1048576,0)),"")
L3L3=IFERROR(INDEX(SHIFTS!G$3:G$1048576,MATCH(L2,SHIFTS!F$3:F$1048576,0)),"")
M3M3=IFERROR(INDEX(SHIFTS!H$3:H$1048576,MATCH(L2,SHIFTS!F$3:F$1048576,0)),"")
N3N3=IFERROR(INDEX(SHIFTS!L$3:L$1048576,MATCH(N2,SHIFTS!K$3:K$1048576,0)),"")
O3O3=IFERROR(INDEX(SHIFTS!M$3:M$1048576,MATCH(N2,SHIFTS!K$3:K$1048576,0)),"")
P3P3=SUM(C6,E6,G6,I6,K6,M6,O7)
Q3Q3=IFERROR(P3/COUNTIF(B2:O2,"*wd*"),"00:00")
B4,D4,F4,H4,J4,L4,N4B4=IF(B2="","","Total")
C4,E4,G4,I4,K4,M4,O4C4=IFERROR(C3-B3,"")
B5,D5,F5,H5,J5,L5,N5B5=IF(B2="","","Break")
C5,E5,G5,I5,K5,M5,O5C5=IFERROR(C4-C6,"")
B6,D6,F6,H6,J6,L6,N6B6=IF(B2="","","Paid Time")
C6C6=IFERROR(INDEX(SHIFTS!D$3:D$1048576,MATCH(B2,SHIFTS!A$3:A$1048576,0)),"")
E6E6=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(D2,SHIFTS!F$3:F$1048576,0)),"")
G6G6=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(F2,SHIFTS!F$3:F$1048576,0)),"")
I6I6=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(H2,SHIFTS!F$3:F$1048576,0)),"")
K6K6=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(J2,SHIFTS!F$3:F$1048576,0)),"")
M6M6=IFERROR(INDEX(SHIFTS!I$3:I$1048576,MATCH(L2,SHIFTS!F$3:F$1048576,0)),"")
O6O6=IFERROR(INDEX(SHIFTS!N$3:N$1048576,MATCH(N2,SHIFTS!K$3:K$1048576,0)),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L2:M2Cell Valuecontains "REST"textNO
J2:K2Cell Valuecontains "REST"textNO
H2:I2Cell Valuecontains "REST"textNO
F2:G2Cell Valuecontains "REST"textNO
B2:C2Cell Valuecontains "REST"textNO
C5Cell Valuebetween 0.000694444444444444 and 0.0305555555555556textNO
N2:O2Cell Valuecontains "REST"textNO
D2:E2Cell Valuecontains "REST"textNO
O5Cell Valuebetween 0.000694444444444444 and 0.0305555555555556textNO
M5Cell Valuebetween 0.000694444444444444 and 0.0305555555555556textNO
K5Cell Valuebetween 0.000694444444444444 and 0.0305555555555556textNO
I5Cell Valuebetween 0.000694444444444444 and 0.0305555555555556textNO
G5Cell Valuebetween 0.000694444444444444 and 0.0305555555555556textNO
E5Cell Valuebetween 0.000694444444444444 and 0.0305555555555556textNO
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

padapinto

New Member
Joined
Sep 21, 2020
Messages
42
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Oct 14, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 21, 2020
Messages
42
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Oct 14, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 21, 2020
Messages
42
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Oct 14, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,931
Messages
5,627,690
Members
416,266
Latest member
stevenvanroeden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top