Colour cells between 2 times but leaving out lunchbreaks

Jackie Grant

New Member
Joined
Apr 30, 2017
Messages
10
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
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>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hello,

Just to better understand ... If you do not mind ... ... a few questions :

1. What is the actual formula you are using in cell H5 ...?
2. With a one-hour Lunch break, in row 5 for example .. do you actually need to have both L & M Columns empty ...?
3. What is the current conditional formatting formula you are using ...?
 
Upvote 0
Jackie, welcome.

Maybe....
Sheet3

*ABCDEFGHIJKLMNOPQR
4NameStartLunchLunchFinishSite amSite pm08:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:00
5Bob08:0012:0013:0018:00ABCD***********
6Beryl10:00**16:00CDCD***********
7Belinda08:0013:0014:0018:00CDAB***********
8Bert10:00**14:00ABAB***********

<tbody>
</tbody>

Edit: ExcelJeanie playing up

can't post CF formulas properly ???? will try another post
 
Last edited:
Upvote 0
Hi James, of course I don't mind and thank you so much for looking at this for me.

In H5 it would be:
=IF(AND(H$4>=$B5,H$4<=$E5),$F5," ")

The L&M columns don't have to be empty if there is a lunchbreak, but they need to be different, to show that the person would not be there at that time, as I would need to arrange cover from elsewhere.

The conditional formatting is:
Choosing Cells in columns H to M (morning)
If cell value is equal to CD then fill Green with same colour green text
If cell value is equal to AB then fill Purple with same colour purple text

Choosing Cells in columns N to R (afternoon)
If cell value is equal to CD then fill Green with same colour green text
If cell value is equal to AB then fill Purple with same colour purple text

It's a bit messy I think, but I works. All except for how to show where the lunches are :)
 
Upvote 0
Strange?

CF formula for green

Code:
<if($c5 =="" "",$e5,$c5)),$f5,if(and(h$4<="$E5,H$4">=IF(AND(H$4>=$B5,H$4 < IF($C5 = "",$E5,$C5)),$F5,IF(AND(H$4<=$E5,H$4 > IF($D5 = "",$B5,$D5)),$G5,""))="AB"


for purple

Code:
<if($c5 =="" "",$e5,$c5)),$f5,if(and(h$4<="$E5,H$4">=IF(AND(H$4>=$B5,H$4 < IF($C5 = "",$E5,$C5)),$F5,IF(AND(H$4<=$E5,H$4 > IF($D5 = "",$B5,$D5)),$G5,""))="CD"


*** this is just a CF representation and is not requiring nor putting any values into th cells ???????</if($c5></if($c5>
 
Last edited:
Upvote 0
Hi Tony, thank you so much for looking at this for me. The picture you posted looks to be exactly what I am trying to do, but I can't quite make the formulae work.
However that's probably me, not you :)

By using that formula, with a little tweak (just to make it work with my actual spreadsheet), it is returning values of True and False. Whereas my conditional formatting is looking for values of AB or CD.

All the Trues and Falses are in the right place though :?
=IF(AND(H$4>=$B7,H$4 < IF($C7 = "",$E7,$C7)),$F7,IF(AND(H$4<=$E7,H$4 > IF($D7 = "",$B7,$D7)),$G7,""))=$F7
 
Upvote 0
Not sure if it helps, but columns F&G are manually input, depending on where I need to put people.
The greens and purples of the conditional formatting then show if I have enough cover or need to move people around between site AB and site CD.
 
Upvote 0
Snakehips has provided you with the conditional formatting formulas ... for the whole range ...

Should you need to use these formulas in your spreadsheet ... just remove the end section ="AB" ...

For example in cell H5 :

Code:
<if($c5 =="" "",$e5,$c5)),$f5,if(and(h$4<="$E5,H$4">=IF(AND(H$4>=$B5,H$4 < IF($C5 = "",$E5,$C5)),$F5,IF(AND(H$4<=$E5,H$4 > IF($D5 = "",$B5,$D5)),$G5,""))</if($c5>
 
Last edited:
Upvote 0
Sorry for any confusion but I have had a few gremlins trying to post the formulas.

I assumed that you wanted formulas for the CF rather than formulas to create values in the time slot cells.
If that is the case then you should be able to use the two formulas, as is now, in post #5
Because they are cf formulas they are returning TRUE or FALSE

If you want the values "AB", "CD" or "" returned to the cells then use

Code:
  =IF(AND(H$4 > =$B5,H$4 < IF($C5 = "",$E5,$C5)),$F5,IF(AND(H$4 < =$E5,H$4 > IF($D5 = "",$B5,$D5)),$G5,""))

in H5 and drag across and down.
Then your existing cf formulas should be fine.
 
Last edited:
Upvote 0
Hi Jackie,

To be on the safe side ...

Just make sure the formula is in line with your actual working schedule ...

With a one-hour Lunch break, for example in row 5, ... is it your objective to actually have both L & M Columns empty ...???
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top