Conditional Formatting to Exclude Weekends
 

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!

Conditional Formatting to Exclude Weekends
 

On Fri, 25 May 2007 09:26:01 -0700, 2manypuppies
wrote:

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

Conditional Formatting to Exclude Weekends
 

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

"2manypuppies" wrote:

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!

Conditional Formatting to Exclude Weekends
 

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(A2TODAY(),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(A2TODAY(),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" wrote:

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!

Conditional Formatting to Exclude Weekends
 

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

Cheers!

Cortney

"andy62" wrote:

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

"2manypuppies" wrote:

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!