Conditional Formating for time scheduled

[TFSRZR]

I'm trying to create a schedule chart that allows me to see what periods of a range have been scheduled. I have been able thru conditional formatting to highlight the cells yellow for the scheduled time (start / end). I’d like to be able to have two different highlight colors. I tried various conditional formulas to try to get the time blocks to highlight black where Unit in column A is X and Yellow highlights for all other units , but couldn't get it to work. I have posted sample spreadsheet to one drive https://1drv.ms/x/s!Ai2z2uOS2mdJbIvl6UrlCSbhd3A

Thanks

 

[thisoldman]

Try this.

Applies to $E$2:$AL$15
Black formula: =AND(E$1>=$C2,F$1<=$D2,$A2="X")
Gold formula: =AND(E$1>=$C2,F$1<=$D2)

 

[etaf]

I have used
OR(AND(E$17>=$C18,E$17<=$D18),AND($C18>=E$17,$C18<=E$17))

That will confirm the CELLS to use

Now you can ADD the X
AND($A18="X",OR(AND(L$17>=$C18,L$17<=$D18),AND($C18>=L$17,$C18<=L$17)))

now use that as the formula in conditional formatting and use Black fill colour - stop if true ticked

then a 2nd rule
AND($A18<>"X",OR(AND(L$17>=$C18,L$17<=$D18),AND($C18>=L$17,$C18<=L$17)))
for your other colour

BUT you have Dates in the title times - you need to remove all the Dates snf just have time

 

[TFSRZR]

Thanks, it works!

 

[TFSRZR]

Thanks thisoldman and etaf! It works!!

 

[thisoldman]

Glad to have helped. Thanks for letting us know the solutions worked.

 

[etaf]

you are welcome