Conditional formatting problem with work-days vs. week-ends

[robertSYNCREON]

Hello:


I have not been able to find an answer on the forums for this, so:


I have a worksheet with due dates, and need to format them as such:

• past due [i.e., A1<TODAY()]

• due today [i.e., A1=TODAY()]

• due tomorrow [i.e., A1=TODAY()+1]

• due in 2 days or more [i.e., A1>=TODAY()+2]


So far, no problem, but I need to "ignore" week-ends (as in if A1 is due on Monday, format the cell on Friday to reflect as "due tomorrow", not "due in 2 days or more").


I thank you in advance for the assistance!


-Robert

 

[Luke M]

Welcome to the forums!


Hopefully you're using 2007 or later, otherwise the 4 conditions could be trouble. The first two conditions shouldn't have any changes (whether it's the weekend or not, the past is the past).


For condition 3:

=NETWORKDAYS(TODAY(),A1)-1=1

Condition 4:

=NETWORKDAYS(TODAY(),A1)-1>1


Note that the Analysis ToolPak add-in needs to be activated.

 

[fred]

Hi robertSYNCREON


other hint: if you want to know if a certain date is weekend you can use Weekday(serial number,1) and when you formulate. All 6 and 7 are Saturday and Sunday you can avoid in your formatting.

 

[robertSYNCREON]

Luke:


I am using 2007 with Analysis Tool-Pak. I used your suggestions, and all seems to be working well...we will see how that works come Friday.


Thanks so very much for the quick reply, and let me say this is my new favorite site...well, for work that is ;-)


EDIT: Thanks for the tip as well, Fred.