Conditional formatting question - Excel 1

[EcoWill]

Hi T.T. - I know this is a conditional formatting plus date function question, but just not sure how to combine the two. I will use the =now() function as a reference. If a future date in a cell that is 'written in' (like September 15, 2011) is within 1 week i want the cell color to be red or yellow or something that stands out. Not sure how. Thanks.

 

[AnotherHiggins]

The criteria for your Conditional Format will be a formula:

<=today() + 7

(The Now function includes the time. Today just returns today's date)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[EcoWill]

That is fine - the coworker who wants this answer wants red for next week (7 days or less) yellow for 8-14 days blue for 15-30 days. The colors will be in a separate cell than the date. Make sense? I hope

 

[xlhelp]

If date is in column B then conditional format criteria will be in the following sequence
=B2<=Today()+7 [red]Format Red[/red]
=B2<=Today()+14 [yellow]Format Yellow[/yellow]
=B2<=Today()+30 [blue]Format Blue[/blue]



Canadian eh! Check out the new social forum Tek-Tips in Canada.
I thought I had retired...I was mistaken. I used to work for a large corporation; now I work for my wife.

 

[Jabb23]

This ia a mock of an excel sheet
A1 A2
09/09/2011 =IF(A1<=(TODAY()+7),0,1)

Copy down for as many rows as are required.
Then do conditional formating based on 1 or 0

 

[SkipVought]

If a future date in a cell that is 'written in' (like September 15, 2011)

When people deal with DATES, there is often a question of whether the DATES are actual DATES or just TEXT. Just be certain that your values are actual DATES!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EcoWill]

Ok thanks for that, but I do not see where I am choosing a color. If I want yellow, where is that in the formula? How about Green? Etc. And then there is the other issue - how then do i do a range to say, from 8-15 days, then yellow? Is it possible?

 

[SkipVought]

You enter a formula and then hit the FORMAT button to select what kind of format youi need.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Deniall]

Using Excel-2010, when I apply the three rules given by Xlhelp (7 Sep 11 @ 23:43 above) in a conditional format, I get exactly the result you appear to want.

My recollection, quite possibly wrong, is that with earlier versions of Excel you were limited to only two co-acting conditions in a conditional format.

 

[xlhelp]

Deniall.

It was a total of three for 2003 and preceding versions.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I thought I had retired...I was mistaken. I used to work for a large corporation; now I work for my wife.