conditional formatting - dates

[Roger]

Hi
I'm using Excel 2007 and would like to know how to conditionally
format dates so the color changes as per:
Green - the last 3 days
Orange - 4 to 7 days old
Red - older than 7 days

Is there a single formula that can do this under the conditional
formatting or do I need to do 3 rules?

Cheers

 

[JE McGimpsey]

You need at least 2 rules. You could set one format as the default (say
red). Then

CF1: Formula is =(A1-TODAY())<=3
Format1: <green>

CF2: Formula is =(A1-TODAY())<=7
Format2: <orange>

or you could leave the cell(s) formatted as general and add the third CF:

CF3: Formula is =ISNUMBER(A1)
Format3: <red>

 

[Roger]

Thanks for the help.

Your formula works if rearranged to
=(today()-A1)<=3
etc as the today value will always be the highest in my application.

If I have a column with cells formatted to 'custom d mmm', then enter
just the number of the day rather than the full blown 'day/month'
entry (ie 18/10 for 18th Oct), the conditional formatting will not
pick this up!

Conditional formatting seems to respond to keyboard entry of 'day/
month', and seems to disregard the underlying cell formatting setup
with 'custom'.

Am I missing something here?

 

[Shane Devenshire]

Hi,

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, Manage Rules, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
1st condition =A2<TODAY()-7
2nd condtion =A2<TODAY()-3
3rd condtion =A2<TODAY()
5. For each of these Click the Format button and choose a format.
6. Between each of these Click OK twice and click New Rule
7. Make sure the rules are in the order stated above, and turn on the Stop
If True boxes for the first two rules. Then click OK.

You may need to adjust the formulas based on how you define the last 3 days
(does it include today or not?)

This assumes your dates are in A2:A20, adjust accordingly.

To your latest question:
If you are using US system dates then an entry of 18/10 is treated as text
by Excel. If you enter it as 10/18 then Excel will assume Oct 18th of the
current year.
So there is nothing wrong with the condtional formatting only the way you
are entering the dates. Just enter them as month/day and format them any way
you want.

By the way formatting 18/10 to d/mmm has not effect at all on text entries,
but it will on legal Excel dates.