Conditional Formatting of Scheduled Times outside of Staff Availability

Hello,

This is my first post, so I hope I give enough information.

I want to conditional format any cell within a range that falls outside of the posted availability of my staff, I have a couple of wishes for this to happen and they are:

1. Not to have to execute the conditional formatting for every single cell individually. (I have hundreds of cells) All "Staffing Availability" is on a separate worksheet from our schedule.

2.To find the condition / function to work around the scheduled time if it falls past the 24hr mark as in 12:00 AM (example shift 9:00 pm - 5:00 am)



Below is a snippet of an example schedule with availability:

Example Schedule Data.png


I appreciate any input and thank you for time spent helping in advance, I'm lost as to how to handle this scenario.

Debbie

The availability data is clear enough, but please show us what the cells look like that you want to format. For each cell, how do we know who the applicable staff person is, and what time to check for availability?

It would be a lot easier if you attached a sample Excel file instead of a screen shot (see yellow banner at the top of the page). Then we can test a solution and give your file back to you knowing it will work.

Jeff,

Thank you, I'm still trying to figure out forums in general, but I believe i've uploaded the file now.

Times to check would be Shift Start vs Availability Start, Shift End vs Availability End.

Debbie