Jackie Grant
New Member
- Joined
- Apr 30, 2017
- Messages
- 10
Hi Guys, I'm stuck 
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
<tbody>
</tbody>
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 | P | 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 | ||||||||||
<tbody>
</tbody>
