I'm trying to create a chart using conditional formatting that will show the hours that each employee is working. I have a formula for that works for Chris' time but can't figure out a formula that covers both Chris & Richard's schedule since Richard is working overnight. Richard's hours between 2200 hrs & 600 hrs need to be filled in with a color & Chris' hours need to be filled in also to show his hours.
<tbody>
</tbody>
<tbody>
</tbody>
SUN | MON | TUES | WED | THURS | FRI | SAT | ||||||||
28-Jul | 29-Jul | 30-Jul | 31-Jul | 1-Aug | 2-Aug | 3-Aug | ||||||||
START | END | START | END | START | END | START | END | START | END | START | END | START | END | |
RICHARD (RB) | 2200 | 600 | 2200 | 600 | 2200 | 600 | 2200 | 600 | 2200 | 600 | ||||
CHRIS (CR) | 900 | 1700 | 900 | 1700 | 900 | 1700 | 900 | 1700 | 900 | 1700 |
<tbody>
</tbody>
DATE | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | 1000 | 1100 | 1200 | 1300 | 1400 | 1500 | 1600 | 1700 | 1800 | 1900 | 2000 | 2100 | 2200 | |
<tbody>
</tbody>