Creating Schedule with Conditional Formatting

**jg0219** · 02-07-2023, 01:40 PM

I am trying to create a schedule template to use at my store. I have added conditional formatting to the Barista part of the schedule. We have three different shifts to cover (open, mid & close). I added rules to color code each when a "time in" has been chosen. What I am having issues with is the closing shifts because they vary for time in. Example on Saturdays a mid can be 3pm-8pm but another barista can have 3pm-10pm which is closing but since the rule is already selected to be blue for 3pm, it does not apply the closing rule. I am not sure what else I can do to help me resolve this issue.

**6StringJazzer** · 02-07-2023, 10:21 PM

Welcome to the Forum jg0219!

I have replaced all your rules with three rules for all IN columns and three rules for all OUT columns.

All columns are colored orange if the OUT time is closing time for that day. An XLOOKUP formula in the rule finds closing time for that day.
All columns are colored green if the IN time is opening time for that day. An XLOOKUP formula in the rule finds opening time for that day.
If neither of the above rules apply, and the cell is not blank it will be colored blue. I am assuming that if it's not opening and it's not closing, it has to be MID.

Aside from the colors you might want to refine your data validation to prevent disallowed shifts, like working 8:00 AM to 10:00 PM.

Also, never used merged cells. I got rid of all of those.

**jg0219** · 02-09-2023, 02:56 PM

Thank you so much for your help! I can see the changes you made and they were a great help. Is there a way to communicate or collaborate on the file to see how you did. There is still one column that is not working quite how I'd like it to but I'm not sure if it is possible to do so.

**6StringJazzer** · 02-09-2023, 11:15 PM

I can write it up tomorrow, but I can't commit to doing any kind of live calls for this forum, otherwise I would never get anything else done. What is the one column, what is happening, and what do you want to happen instead?

Creating Schedule with Conditional Formatting

LinkBack

Thread Tools

Rate This Thread

Display

Creating Schedule with Conditional Formatting

Re: Creating Schedule with Conditional Formatting

Re: Creating Schedule with Conditional Formatting

Re: Creating Schedule with Conditional Formatting

Thread Information

Users Browsing this Thread

Similar Threads

Conditional Formatting - 52 week schedule, highlighting weeks at conditional intervals.

[SOLVED] Conditional Formatting a Schedule

[SOLVED] Conditional Formatting a Schedule

Conditional Formatting a Schedule

Conditional Formatting a Schedule

Conditional Formatting a Schedule

Conditional Formatting a Schedule

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions