Help with error if missing a scheduled position on a shift

Status
Not open for further replies.
S

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152
hello ive been trying for years to get an answer on this: This is what i would need help with.

i have the following shifts on any GIVING DAY REQUIRED.

Am cook, Pm cook, am1, am2, am3, pm4, pm 5 & pm6

i would like a formula that if any of these shifts are missing on any giving day a designated cell will tell me exactly which shifts are missing for this day even if 3 are missing. i have a formula now but doesnt work well it works only for a 1 shift but i need an alert telling me which exact shifts are missing per day. currently i just have a conditional formula telling me when were under our daily hours needed that helps me see whats missing. but doesnt work well compared to know exactly whats missing considering we have accounts with up to 100+ employees and know whos missing is very hard.

thank you all
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Steve,

Here is one approach that uses some formulae and data validation (drop downs) to make shift assignments.
Assuming you have just two categories of workers, ie. 'Cooks' and 'Other'
Make two lists, one for 'Cooks', the 2nd for 'Other'. I did that in columns L and N.

Sheet1

KLMNO
#ShiftsCooks Other#Shifts
Bob Lenny
George Mark
Les Marty
Sam Mike
Steve Sebough
Terry

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
K2=COUNTIF($B$2:$I$8,"*"&$L2&"*")
O2=COUNTIF($B$2:$I$8,"*"&$N2&"*")
K3=COUNTIF($B$2:$I$8,"*"&$L3&"*")
O3=COUNTIF($B$2:$I$8,"*"&$N3&"*")
K4=COUNTIF($B$2:$I$8,"*"&$L4&"*")
O4=COUNTIF($B$2:$I$8,"*"&$N4&"*")
K5=COUNTIF($B$2:$I$8,"*"&$L5&"*")
O5=COUNTIF($B$2:$I$8,"*"&$N5&"*")
K6=COUNTIF($B$2:$I$8,"*"&$L6&"*")
O6=COUNTIF($B$2:$I$8,"*"&$N6&"*")
O7=COUNTIF($B$2:$I$8,"*"&$N7&"*")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Then in column A I put the Days (1, 2, 3, etc), or Sunday to Saturday for the entire week or month.
Row 1 is the header row where I put the positions for the cooks and others…see the screenshot below.

Sheet1

ABCDEFGHIJ
DayAM CookPM CookAM1AM2AM3PM4PM5PM6
BobGeorgeTerryLennyMarkMikeSeboughMarty
Missing
LesSteve LennyTerryMarkSebough Missing
Missing
SamBobTerryLennyMarkMikeMartySebough
Missing
Missing

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"]7[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
J2=IF(COUNTA(B2:I2)<>8,"Missing","")
A3=A2+1
J3=IF(COUNTA(B3:I3)<>8,"Missing","")
A4=A3+1
J4=IF(COUNTA(B4:I4)<>8,"Missing","")
A5=A4+1
J5=IF(COUNTA(B5:I5)<>8,"Missing","")
A6=A5+1
J6=IF(COUNTA(B6:I6)<>8,"Missing","")
A7=A6+1
J7=IF(COUNTA(B7:I7)<>8,"Missing","")
A8=A7+1
J8=IF(COUNTA(B8:I8)<>8,"Missing","")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Then I created 2 two data validation (dropdown) lists, one for Cooks and the 2nd for the Other shifts.
So when you select a cell in the range B2:I7 a dropdown arrow is displayed, press the arrow and a list of
names is shown. If you select columns B or C you will get just the Cooks names. When you select columns
D to I you will get just the Other names. I found it handy to keep track of how many times each name is assigned
so I have a formula that does that by each name as show in the first screenshot above (columns K and O).
If this looks like something you can use, let me know and I will provide the formulas and directions how to create
the sheet. I can also provide a copy of the file using Box.com if you prefer so all you have to do is change the names in columns L and N.
Let me know if this something you would like to look at.
Perpa
 
Upvote 0
thank i like this and truly appreciate it very much but unforunately its not what i was looking for i did try it and it works but im looking for something to specify exactly whats missing for example "missing am 1" and if another shift is missing such as "am 2" its will say Missing am 2
 
Upvote 0
Steve,
One additional thought with reference to the second screenshot I sent previously, try the following:
In J1, type 'Missing'. Paste the following formula into J2. Then copy that formula down column J to the last Day shown in column A.

Code: 
=IF(B:B="","AMCook ","")&IF(C:C="","PMCook ","")&IF(D:D="","AM1 ","")&IF(E:E="","AM2 ","")&IF(F:F="","AM3 ","")&IF(G:G="","PM4 ","")&IF(H:H="","PM5 ","")&IF(I:I="","PM6","")

Adjust the column J width as needed.
Perpa
 
Last edited:
Upvote 0
Status
Not open for further replies.

Similar threads

D
  • Question Question
Formula to "Review" Alternating Shift Schedules
Replies
0
Views
143
davidcox9
D
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
150
Tetra201
Tetra201
G
  • Solved
Excel functions to determine hours worked on day or night shift
Replies
7
Views
524
Kaper
Kaper
S
  • Solved
Counting actuals that have exceeded or missed a target by percentages
Replies
5
Views
79
SPEARSON1234
S
G
  • Question Question
Help with formula time frame
Replies
3
Views
44
GenAkaman
G

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top