conditional formatting based on 2 seperate rules

[steve_layton]

I have just started a new job where I'm responsible for shift planning.

I've set up the roster to count how many people we have on shift each day.

If we have more than 6 people on shift Sun - Thu I want the box with the count in to turn green.

I have managed that part already 🙂

However on Fri & Sat we need more than 8 people on shift. I'm struggling to set a second rule that checks what day it is and turns green if above 6 sun-thu and turns green if above 8 on fri and sat.

can anyone help please?

I've included a photo below but just realised I've cut off the row numbers "FRI" sits in D3. The row of green sevens is row 14.

TIA 

[#2]

@steve_layton 

To achieve conditional formatting based on two separate rules, one for Sun-Thu and another for Fri-Sat, you can use a combination of Excel's IF function and conditional formatting rules. Here's how you can set it up:

1. Select the range of cells where you want the conditional formatting to apply (e.g., the cells containing the count of people on shift).
2. Go to the "Home" tab in Excel.
3. Click on "Conditional Formatting" in the toolbar.
4. Choose "New Rule" from the drop-down menu.
5. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
6. For Sun-Thu (assuming your date is in cell A1):

Enter the following formula:

=AND(A1<>"", WEEKDAY(A1, 2)<=5, A1>6)

• Click on the "Format" button and choose the formatting style you want for when the condition is met (e.g., turn the box green).
• Click "OK" to confirm the formatting.

7. For Fri-Sat (assuming your date is in cell A1):

• Enter the following formula:

=AND(A1<>"", WEEKDAY(A1, 2)>5, A1>8)

• Click on the "Format" button and choose the formatting style you want for when the condition is met (e.g., turn the box green).
• Click "OK" to confirm the formatting.

8. Click "OK" again to apply the conditional formatting rules.

This setup will turn the cells green if there are more than 6 people on shift for Sun-Thu and more than 8 people on shift for Fri-Sat. Adjust the cell references and formatting as needed for your specific setup. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants

[#3]

@NikolinoDE 

The first formula worked but the second didn't.

If you look at column D the "5" indicates someone is on shift.

The count is at the bottom highlighted in green. D4-D11 =7 people on shift so it turns green.

But it should still be white as it's a Friday and it needs to exceed 8.

Thanks for your help.

[#4 · Accepted solution]

@steve_layton 

See the attached workbook for alternatives.

[#5]

OMG @SnowMan55 I don't know how but this worked!!! I used the last option the SWITCH formula and HEY PRESTO it changed green when I needed it to. I didn't really understand the formula or the instruction but it works. Thank you so much for your help!!!