Conditional formula to Turn cells white when cell inbetween specific time range

[ashaw2]

Hi,

I am creating a staff planner and need certain cells to turn white when between a time period. For example: sales assistant starts at 11 finishes at 3. I need the cells between this time range to turn white to indicate they are available to work. See example attached.

Start times are in Cell F46 and finish time J46. I need cells in X46 and AF 46 to turn White (leaving the other cells black that are already filled)

[etaf]

can you load a sample sheet
a few ways to do that , I would just put a 1 into the cell where it matches
then have that as black fill and font , then change to a white fill and font via conditional formatting

[ashaw2]

Hi,

Thanks for your feedback. I've attached a sample sheet. The cells are already conditioned to change colour depending on certain text inputted so I wont be able to add alternative text to change the colour of the cell that way?

[etaf]

so if the cell changes based on different conditions - what happens exctly

DRTOS
I see colours based on those letters
how are they entered

also your columns are not entered as TIME - can those labels be chnge to a time rather than TEXT

you can see the formula in the first row - just for an example , not needed in the cell
=AND($F46<=T$45,$J46>=T$45)

But you may need to use STOP if true and reorder the conditional formatting , depending on what rule you want to apply and stay fixed

[ashaw2]

Hi,

The letters are entered manually by the user on the day based on the key. I can change the start and end times to TIME. What do you suggest going forward then?

[etaf]

as shown in the attached sheet, in my last post, i have changed the columns to time, you can reformat those to display as you like , if you want AM/PM
BUT the order of the rules maybe important
what happens IF one of the letters entered ? that colour needs to be first in list and stop if true

[ashaw2]

Hi,

Thankyou for this! This works fine on your example but im having trouble brining this over to my own sheet? It just seems to fill all the boxes in black when inputting times regardless of what times are entered every cell is turned black?

[ashaw2]

Thanks! All working now. Thankyou for your help! managed to sort it!

[etaf]

excellent, very welcome