Employee scheduler

Hi,

first, you should get rid of the merged cells on sheet 2. They can lead to all kinds of trouble. Use the horizontal alignment option "Center acoss selection" on the Alignment tab of the formatting box instead.

What is the rule behind how many hours should be yellow, how many green, how many blue? This does not come from your data, as far as I can see. You'd need to give Excel something to work with.

Oh, and the formatting for the break time is using borders. These can not be set with conditional formatting, but the background colour should be fine, as long as there are only three conditions.

So, can you give us a bit more info?

See attached, based on which I would make a few points:

1. You should bring across start/end times from Sheet1 so as to avoid conducting repetitive calculations on Sheet2 (see AO/AP on Sheet2)

2. You should use a 24 scale in your headers (simplifies things for you see Sheet2!B1:AM1)

3. By setting a value in the Matrix itself (G/Y) you could simplify your conditional formulae (you could set the conditional formats to use a text colour = fill colour such that the text is not visible) .. you could do the calcs in the conditional formulae direct but IMO it is easier to audit/adapt when the formulae are in cells directly ... a case of personal preference.

4. There was some discrepancy in your setup re: shifts (9pm or 9.30pm finish?)- if the shifts are inconsistent things will get a lot more complex very quickly.

Regardless, I hope this helps a little.