Conditional Formatting to Exclude Weekends

[2manypuppies]

Greetings!

I am using conditional formatting to show when a due date is coming up based
on today's date. I have it set up so when the due date is 2 days out the
cell turns green, 1 day out turns yellow, and today the cell turns red. How
can I conditionally format so Saturday & Sunday aren't figured in as days?

ie. If something is due on Monday, I want that cell to turn green on
Thursday & yellow on Friday.

My formula is currently set up using the TODAY(), TODAY()+1 & TODAY()+2
functions for each color.

THanks!

 

[Ron Rosenfeld]

Greetings!

I am using conditional formatting to show when a due date is coming up based
on today's date. I have it set up so when the due date is 2 days out the
cell turns green, 1 day out turns yellow, and today the cell turns red. How
can I conditionally format so Saturday & Sunday aren't figured in as days?

ie. If something is due on Monday, I want that cell to turn green on
Thursday & yellow on Friday.

My formula is currently set up using the TODAY(), TODAY()+1 & TODAY()+2
functions for each color.

THanks!

=SUMPRODUCT(--((ROW(INDIRECT($A$2&":"&B2))*(WEEKDAY(ROW(INDIRECT($A$2&":"&B2)),2)<6))>0))<=1
=SUMPRODUCT(--((ROW(INDIRECT($A$2&":"&B2))*(WEEKDAY(ROW(INDIRECT($A$2&":"&B2)),2)<6))>0))=2
=SUMPRODUCT(--((ROW(INDIRECT($A$2&":"&B2))*(WEEKDAY(ROW(INDIRECT($A$2&":"&B2)),2)<6))>0))=3

Ignores the weekends.

A2 is the due date
Substitute TODAY() for B2


--ron

 

[andy62]

Use these in your three conditions:
Red: =TODAY()
Yellow: =TODAY()+1+IF(WEEKDAY(A1,2)<3,2,0)
Green: =TODAY()+2+IF(WEEKDAY(A1,2)<3,2,0)

Where A1 is the current cell. Make sure it doesn't show up as $A$1 because
you'll want to copy the format to all your other cells.

HTH

 

[Ron Coderre]

Try this:

With
A2: (a date)

These formula can be used in Conditional Formatting:

RED
This formula returns TRUE if A2 is LESS THAN OR EQUAL TO TODAY
=(A2<=TODAY())

YELLOW
This formula returns TRUE if A2 is ONE workday after TODAY
=IF(AND(A2>TODAY(),WEEKDAY(A2,2)<6),(A2-(TODAY()+CHOOSE(WEEKDAY(TODAY(),2),0,0,0,0,2,2,1)))=1,)

GREEN
This formula returns TRUE if A2 is TWO workdays after TODAY
=IF(AND(A2>TODAY(),WEEKDAY(A2,2)<6),(A2-(TODAY()+CHOOSE(WEEKDAY(TODAY(),2),0,0,0,0,2,2,1)))=2,)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[2manypuppies]

Thank you both for your help. They work like a champ!

Cheers!

Cortney