formatting excel date list

R

rslc

Hi
i have a list of dates ie 01/02/09
03/02/09
07/02/09

what i would like to happen is when the last date exceeds the
previous date by more than 5 days, highlight it in red and if it less than 5
days older highlight it green. i have tried in cell formulas and conditional
formatting but so far no luck Appreciate any help

thanks

rslc
 
P

Pete_UK

Suppose your dates are in column A from A1 onwards. Highlight the
cells from A2 onwards (i.e. omitting the first date) as far as you
think you will need, and use this formula for the first conditional
format rule:

=AND(A2=LOOKUP(10E10,A:A),A2>A1+5)

and format that as red. Use this formula for your second condition:

=AND(A2=LOOKUP(10E10,A:A),A2>A1,A2<=A1+5)

and format that as green. OK your way out of the CF dialogue box.

Hope this helps.

Pete
 
R

rslc

HI Pete
thanks thats just what i was hoping to do, although the colour
change reverts to default ( black ) when i add another date to the column .
any way to make the cells stay changed when i add another date

cheers

rslc
 
P

Pete_UK

Well, you did say that you wanted the last date to be formatted
according to the previous one. Perhaps you did not highlight
sufficient cells when you set up the CF to cover the addition of newer
cells - in that case use the Format Painter icon to extend the CF
further down the column. If you mean that you want all dates to have
the appropriate format applied to them (not just the last one), then
you can change the formulae to:

=A2>A1+5

and format that as red, and for your second condition:

=AND(A2>A1,A2<=A1+5)

Note that these expect A2 to be the active cell in a highlighted
range.

Hope this helps.

Pete
 
P

Pete_UK

You're welcome - thanks for feeding back.

Pete

Hi Pete
            thanks thats perfect sorry i wasn't clear enough

                                                        cheers
                                                                    rslc







- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

percentage of green cells in list 3
Due date - change cell color 2
Formulas with dates 3
Date Function 2
Copy value according to date 1
Lookup Wizard 4
Count based on date criteria 1
Complex function needed? 5

Top