Conditional Date Formatting
 

I have a spreadsheet tracking dated shipments from our suppliers to our
customers.
I am showing the rows for shipments that are due to go out in 7 days
(or less) shaded in red, and those that are 14 days out in green ----
=$A2-TODAY())< 7 ....

=$A2-TODAY()<14.....

Rows for shipments due to go out 6/1 or earlier are shaded green, while
those due on 6/2 are not shaded - which is what I wanted.
Thinking about it, it might be better if the "green" rows (8-14 days
out) could be expanded to include the end of the workweek - in this
case, 6/1 is a Thurs, but it would be better to include 6/2, which is
Friday.
Is there a better function (with arguments) to use in this formula?

Also....
The empty rows at the end of the workbook are all shaded red. Can I add
the condition that
$A2 NOT ='' ?

Thanks.

Jay

Conditional Date Formatting
 

=AND($A2<"",$A2+CHOOSE(WEEKDAY($A2),5,4,3,2,1,0,-1)-TODAY()<7)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JayD" wrote in message
oups.com...
I have a spreadsheet tracking dated shipments from our suppliers to our
customers.
I am showing the rows for shipments that are due to go out in 7 days
(or less) shaded in red, and those that are 14 days out in green ----
=$A2-TODAY())< 7 ....

=$A2-TODAY()<14.....

Rows for shipments due to go out 6/1 or earlier are shaded green, while
those due on 6/2 are not shaded - which is what I wanted.
Thinking about it, it might be better if the "green" rows (8-14 days
out) could be expanded to include the end of the workweek - in this
case, 6/1 is a Thurs, but it would be better to include 6/2, which is
Friday.
Is there a better function (with arguments) to use in this formula?

Also....
The empty rows at the end of the workbook are all shaded red. Can I add
the condition that
$A2 NOT ='' ?

Thanks.

Jay

Conditional Date Formatting
 

Thanks.... I'm sure never would have figured that out :)