Easy way to mass conditional format?

[RedOctoberKnight]

Good evening,

First off, I would just like to say a big thank you to everyone who contributes to this forum. You're help has been much appreciated.

I'm trying to develop a visual chart for employee staffing and I'm wondering if there is a easy way to conditional format numerous cells on a mass scale.

Basically what i'm trying to do is the the following:

starting in cell B1 and moving across columns is my start times in 15 minute intervals. (my company uses A,B,and C to equal :15,:30 & :45.) EX: 10A would equal 10:15

so starting at midnight in column B and moving across columns, i have 0 in B1, 0A in C1, 0B in D1 , 0C in E1, 1 in F1 and so on all the way to 23C (24hr time)

What i'm looking to do is when I put a value into cell A2 (Let's say 3A), the cells in row 2 will highlight the color red underneath the times in row 1 in 8.5 hour intervals. So the cells in row 2 underneath the column that contains 3A all the way to 11C would highlight the color red.

I've been able to do accomplish this one start time at a time by highlighting each 8.5 block for that time and then setting a CF rule. It's just a very long process.

If this is the only way that it can be done than so be it, but if someone understands what i'm trying to accomplish and can do it in a much more efficient way, it would be much much appreciated.

As always, I appreciate all that everyone contributes.

Thanks.

 

[RedOctoberKnight]

4B would be equal to 4:30 and 8B would be equal to 8:30.

I think I've figured out a way to accomplish this but I'm running into an issue if I try using actual times instead of a quick reference like 4B or 8B.

I've basically run a string of times starting at 0:00 across row 2 starting in column E. (from 0:00 all the way to 23:55 using 5 minute intervals. 5:00,5:05,5:10 etc.)

I created a column for entering start times in column B and I did a column for end times in column C.

I column D I want to find the difference between the two different times.

I've put the formula from above into cell E3, ran it across and then down.

=IF(AND($B3<>"",COLUMN(E3)>=MATCH($B3,2:2,0),COLUMN(E3)<=MATCH($B3,2:2,0)+$D3),"X",)

I've replaced the "+34" in the formula from the posts above to be equal to the difference between the two times. That way it knows how many cells to mark.

The problem I'm having is, it only works for some times and not others.

I can get it to work for start times of 5:00, 5:05, and 5:10 but it wont work again until I get to 6:00. And then not again till 6:30...

I'm completely stumped on why it only works for some times and not all of them.

 

[Marcelo Branco]

Some points
1. Column D (difference) is not necessary

2. IMPORTANT: to not face rounding problems (floating-points calculations) be very careful when creating the values (hours:minutes) in E2, F2, G2.....KF2. Otherwise the MATCH function won't work properly.
Try this
Formula in E2
=TIME(0,5*COLUMNS($E2:E2)-5,0)
copy across till KF2

3. Try this formula in E3 copied across until KF3
=IF(AND(COLUMN(E3)-COLUMNS($A3:$E3)+1>=MATCH($B3,$E$2:$KF$2,0),COLUMN(E3)-COLUMNS($A3:$E3)+1<=MATCH($C3,$E$2:$KF$2,0)),"X","")

M.

 

[RedOctoberKnight]

works perfectly! Thank you so much!

 

[Marcelo Branco]

You are welcome. Glad to help

M.