Forum Discussion
Solved
Conditional Formatting Non-work Days
Hello,
I'm currently building a new calendar for my company. Basically, we have 3 different shifts that each have their own work days/non work days. Below is the brake down for each shift and their respective work days and the conditional formatting formula that highlights their non-work days.
Shift A - Works Monday through Friday and off Saturday and Sunday.
=WEEKDAY(G$1,2)>5
Shift B - Works Sunday through Wednesday and is off Thursday through Saturday.
=WEEKDAY(G$1,1)>4
Shift C - Works Wednesday through Saturday and is off Sunday through Tuesday
=WEEKDAY(G$1,13)>4
What I have been trying to do is when an individual changes shift, I can update column A, to either Shift A, B, or C. Then their row for the calendar is updated to highlight which days are non-work days.
Hi Rusty_Tusty
For this kind of things it's better to share an actual (anonymized) workbook as we have no idea (except dates in G1,H1...) what your setup is. With the below example:
Cond. Format. rule that applies to =$G$2:$R$6:
=SWITCH($A2, "A",WEEKDAY(G$1,2)>5, "B",WEEKDAY(G$1,1)>4, "C",WEEKDAY(G$1,13)>4)Corresponding sample attached...
Some people Search this site and others for existing solutions
You've got 2 options so far. If any of them solves your challenge it would be smart of you to click the Mark as solution link at the bottom of the corresponding post - Thanks in advance
Rusty_Tusty As variant and slightly less advanced. See the attached file?
Thank you for your response this one also works perfectly with what I had in mind. I was initially trying to do this, but I was trying to use the IF function and in one string like the instead of individual formatting. I did play around with your example and realized that I can replace "AND" with "IF" and it still works.
Hi Rusty_Tusty
For this kind of things it's better to share an actual (anonymized) workbook as we have no idea (except dates in G1,H1...) what your setup is. With the below example:
Cond. Format. rule that applies to =$G$2:$R$6:
=SWITCH($A2, "A",WEEKDAY(G$1,2)>5, "B",WEEKDAY(G$1,1)>4, "C",WEEKDAY(G$1,13)>4)Corresponding sample attached...
Thank you for the quick response. I do apologize for initial lack of information, but your answer was what I was looking for and I learned something new. I never knew there was a "Switch" formula. Again, thanks a million.
Glad you have it working as expected & Thanks for providing feedback
FYI the SWITCH function is available with Excel >/= 2019
