Conditional formatting based off range of times in another cell

[christag]

I am looking for a way to automatically fill the cell color of one cell based on a range of times represented in another cell.

So for example, I have column B which lists employee's schedules in the format of 8-4, 9-5, etc. These values are being referenced from another worksheet that contains the master schedule. Next I have times listed in row 6 in the format of 8, 9, 10, etc. What I would like to have happen is if an employee is scheduled to work 12-3, I want the 12, 1, 2 and 3 cells in their respective row to highlight red.

Appreciate any help! TIA.

 

[etaf]

can you upload a sample

Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

use dropbox or onedrive

otherwise I'm not sure i fully understand how the data is structured or if its actually real times or just text , that needs to be converted

 

[christag]

I attached a sample of what it looks like now, and what I'd like as the result. Like I mentioned in my original post, the info in column B is being referenced from another worksheet (the master schedule for the week). Right now the times an employee works are being entered as 8-4 or 9-5 as text, not as a time. I can change that on the master schedule worksheet if that'll effect this outcome. Thanks for your help!

---

can you upload a sample

Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

use dropbox or onedrive

otherwise I'm not sure i fully understand how the data is structured or if its actually real times or just text , that needs to be converted

 

[christag]

https://www.dropbox.com/s/v82zo8l4duy346m/Schedule Sample.xls?dl=0

 

[etaf]

because you are using a 12hr time frame its going to be difficult to tell if the morning or afternoon
say a shift started 6-10 - is that 10 in the morning or 22 in the evening - excel does not know

can you use 24hr format at all ?

 

[christag]

Hi Wayne -

Yes, I can definitely change the formatting of the time.

because you are using a 12hr time frame its going to be difficult to tell if the morning or afternoon
say a shift started 6-10 - is that 10 in the morning or 22 in the evening - excel does not know

can you use 24hr format at all ?

 

[etaf]

i have change the sheet
the times are now entered in row 3 as times in 24hr format
and the shifts are also entered in 24hr text format ie 8-16 for 8am to 4pm

then i have used a rule for cond format
=AND((LEFT($B5,FIND("-",$B5,1)-1))*1<=D$3, (RIGHT($B5,LEN($B5)-FIND("-",$B5,1)))*1>=D$3)

see attached

https://www.dropbox.com/s/5lklzkn3qoln59c/Schedule Sample etaf.xls?dl=0

 

[christag]

This is exactly what I want! Except, that I can't replicate it on my actual worksheet...


I changed my times to the 8-16:00 format. I then copied your formula, altering it so that it was referencing the correct cells. I added a new conditional formatting rule using this:

=AND((LEFT($B7,FIND("-",$B7,1)-1))*1<=D$4,(RIGHT($B7,LEN($B7)-FIND("-",$B7,1)))*1>=D$4)

I selected the color I wanted it to format the cells to be and applied it to the section that I wanted, but it doesn't change anything. I've checked and all of my cells are formatted in the same pattern as the ones in your example (text, general, etc.) Not sure where I went wrong...

Appreciate your help so much, this is going to make my life so much easier once I get it!!

i have change the sheet
the times are now entered in row 3 as times in 24hr format
and the shifts are also entered in 24hr text format ie 8-16 for 8am to 4pm

then i have used a rule for cond format
=AND((LEFT($B5,FIND("-",$B5,1)-1))*1<=D$3, (RIGHT($B5,LEN($B5)-FIND("-",$B5,1)))*1>=D$3)

see attached

https://www.dropbox.com/s/5lklzkn3qoln59c/Schedule Sample etaf.xls?dl=0

 

[etaf]

so the times are still 2 digits

8-16
NOT
8-16:00


and the headings for the times are just numbers - NOT time

so
7 8 9 10 11 12 13 14 etc as a number
NOT time

see if that helps

 

[christag]

Wayne -

That did it! This SO helpful, thank you, thank you!!

Christa