Interesting challenge to highlight instances of >10 consecutive days scheduled

  • Thread starter Thread starter AlanN
  • Start date Start date
A

AlanN

In column A (A12:A377) I have this year's dates by day. In a cell in another column, let's say cell D1, I want to put in a calculation that will present the word "OVER-SCHEDULED" when there is ever a time when someone has been scheduled in to work more that 10 consecutive days in a row. The calculation must look for the following schedule codes to determine a work shift: D,E,N,U,7-11,9-13,8-12,15-19,17-21.

Does anyone have any ideas how this can be achieved?

TIA, AlanN
 
On the worksheet, or on a different sheet in the same workbook, create a
list of the codes that you want to track.
Select these cells, and click in the Name box, to the left of the
Formula bar.
Type a name for the list, e.g. Shifts, and press the Enter key.

Add a column to the table that contains the schedule (cells C12:C377 in
this example)
Add a heading in cell C11 ("Days" in this example)
In cell C12, enter the following formula, and copy down to cell C337:

=IF(COUNTIF(Shifts,B12)=0,0,IF(COUNTIF(Shifts,B11)=0,1,IF(ISTEXT(C11),1,C11+1)))

In cell D1, enter a formula to check column C:

=IF(MAX(C:C)>=10,"Overscheduled","Okay")
 
Back
Top