highlight date if date is in that week?

[mholmes]

I would like to do something that might be hard to do. I have a list of dates. I'd like to highlight them a special color if that date falls in this week, based on the current date.

Any ideas?


Thanks,

Marcus

 

[lenze]

A1 represents the first cell in your column that contains a date. If your dates start in A2, Select the Range A2 down as far as needed. Then, with the cells selected, apply the conditional format, using A2 instead of A1. Excel will automatically adjust the other cells. You can also just do the first cell, and then use the Paint Format Brush to extend the formatting

 

[mholmes]

OK I like it! But really what I want to see is if the dates is within the next 7 days. You know what I mean? Like if this date is next monday it's red. If it's two Mondays from now it's not. If I change the 3 to a 7 it doesn't work.

 

[lenze]

It's almost Miller time. OK to do that, use

=AND(A3>=TODAY(),A3<TODAY()+7)

Edit:Disabled html

_________________
Scotch: Because you don't solve great Excel problems over white wine
This message was edited by lenze on 2002-11-01 17:57

 

[mholmes]

tack that on to the other formula?

 

[lenze]

No. Replace the other formula

 

[IML]

HTML DISABLED. In case Lenze has already hit the scotch (or rather the scotch has hit Lenze) I believe he'd say use this in place of the previous formula, or use these terms in the
Cell Value is
and
Between box
=today()
=today()+7

On 2002-11-01 16:35, lenze wrote:
It's almost Miller time. OK to do that, use

=AND(A3>=TODAY(),A3<TODAY()+7)

 

[lenze]

Good catch IML.

And no, not yet, but a friend just returned from Canada and has a bottle of single malt waiting for me. I will be picking it up shortly

 

[Legacy 11779]

IML's suggested "between" values cover 8 days.

The values should be :-

Either
=Today()
=Today()+6

Or
=Today()+1
=Today()+7

Depending upon how you want to define "the next 7 days".