Another Conditional Formatting Issue

Question

This conditional formatting issue has to do with creating a gantt chart for a work schedule that reveals gaps in coverage. It works pretty good, except it continues to shade cells that the formula doesn't seem to apply to.  As you can see in the photo, I have start and stop times for a 24 hour operation. The column for 2300 is shaded for every employee. I don't understand why.

Answer 1

Thanks, Jeeped. The second formula (the improvement one) worked best. However, there are three cells that still won't work properly. It's for the only two positions I have whose times extend further than my spreadsheet columns. Please see rows 16 and 21. The errored cells are shaded red.

Answer 2

An improvement on that formula to compensate for shifts that cross midnight might be,

=AND(F$1+(F$1<OFFSET(F$1,0,-MIN($D3,COLUMNS($A:A)-1)))>=$C3,F$1+(F$1<OFFSET(F$1,0,-MIN($D3,COLUMNS($A:A)-1)))<$E3)

There is probably a more elegant way to proof that and some of the computation does depend on how you are arriving at E3; e.g. whether it is a typed 14:00 or =C3+TIME(D3,0,0).

Answer 3

Just a side note - I see some #VALUE! errors on some rows.  If you want to keep the formulas in place, but get rid of the "uglies", wrap your formula in an error trap:

Instead of

=yourFormula()

try

=IFERROR( yourformula(), "")

that way it'll appear as an empty cell for you when the error would have come up, but will show the time properly when your formula actually does its job without breaking into tears.

Answer 4

You need to compensate for W1 → X1 crossing midnight where X1 is the equivalent of 0 (00:00). Try,

=AND(F$1>=$C3,(G$1+(G$1<F$1))<=$E3)