Excel: Colour cells between 2 times but leaving out lunchbreaks


Hi Guys, I'm stuck :confused: I am trying to create a template for a work rota which allows me to schedule a team with various start and finish times, some with and without lunch breaks and split across 2 sites. I have managed to cobble together most of it with a combination of formula and conditional formatting, but can't get it to leave the lunchbreaks blank so that I can see where the gaps in cover are on both sites. I'm afraid I am self taught, therefore I don't understand some of the more technical terminology, and haven't figured out how to attach a screenshot, therefore I've tried to give an example of what I've done below - with some of the formula included: =IF(AND(H$4>=$B5,H$4<=$E5),$F5," ") The conditional formatting then colours in those cells which have AB in them in Green, or CD in Purple. Ideally I would like those cells where a lunch break occurs to remain blank. I hope this makes sense, as it's sure confused me. I do hope someone can understand enough to be able to help me and thanks a million in advance. Jackie x :)

A B C D E F G H I J K L M N O P Q R
4 Name Start Lunch Lunch Finish Site am Site pm 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00
5 Bob 08:00 12:00 13:00 18:00 AB CD G G G G P [COLOR=#ee82ee]P[/COLOR] P P P
6 Beryl 10:00 16:00 CD CD P P P P P P P
7 Belinda 08:00 13:00 14:00 18:00 CD AB P P P P P G G G G
8 Bert 10:00 14:00 AB AB G G G G G


This question generated 14 answers. To proceed to the answers, click here.

This thread is current as of April 30, 2017.


For more resources for Microsoft Excel