Conditional Formatting countdown that avoids Weekend Dates

Michael Halvey

Hi all! Long story short - I have design jobs coming into rows that have a Sales Respond Date (to a customer) with customizable turnaround days for edits that then gives the designer a due date to complete the edits of a design (Next Update to Client).

We use conditional formatting in the designer name column to show a quick reference of the "life of the row" before the edit is due.

Green - At least 2 days before

Yellow - Due in 1 day

Red - Due today or past due

Under the "Next Update to Client" column, I added a =WORKDAY formula to ensure that whatever number for the Turnaround Days to Client is put it, it's never on a Weekend date.

Here's where my question comes into play - Conditional Formatting doesn't understand it's the Weekend. So if a job comes in on Friday, and we have 3 days to edit it. The formulas in the sheet understand what day it is and say it's due on Tuesday but the conditional formatting by Monday will say it's Red (counting Sat/Sun) when it should be Yellow still since it's not really due until Tuesday.

Any help with this would be amazing! I'm sure there's some formula I'm missing that could help out with this. Thanks for your time and reviewing my question. 😅

-Michael

Paul Newcome

Try this in another column:

=NETWORKDAYS(TODAY(), [Next Update To Client]@row)

This will have a countdown of week days leading up to the date in question that you can use for your conditional formatting.

Paul Newcome

Try this in another column:

=NETWORKDAYS(TODAY(), [Next Update To Client]@row)

This will have a countdown of week days leading up to the date in question that you can use for your conditional formatting.

Michael Halvey

Thank you for your help with this. I was able to get it sorted!

Paul Newcome

Happy to help. 👍️