Formatting / Formula

[Telford Tom]

Hi,
Not sure if I need conditional formatting or a formula for the following:

If I put a date into a spreadsheet I'd like it to change colour after a set
number of days to warn me that it's now time expired. ie 01 Dec should turn
RED after 90 days to show that time's up!

Any help will be gratefully received

Telford Tom

 

[Jacob Skaria]

1. Select the cell/Range (say A1:A10). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=TODAY()-A1>=90

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.


If this post helps click Yes

 

[Telford Tom]

Sorry, it must be me. This didn't work, things just went straight to Red even
though they're not out of date. In the formula =TODAY()-A1>=90 should I be
typing anything in between the brackets?

 

[Jacob Skaria]

We didnt validate blank entries. TODAY() referes to the current date. Try the
formula = TODAY() in a cell.

Using AND() we will have multiple conditions as below

=AND(A1<>"",TODAY()-A1>=90)

If this post helps click Yes

 

[Telford Tom]

Sorry, still doesn't seem to be working, I'm sure it's me being a bit of a
thicky. I'll try to find some other way of doing it

Tom

 

[Gord Dibben]

Given current date of November 21, 2009 anything before August 24, 2009
should turn to red.


Gord Dibben MS Excel MVP

 

[Telford Tom]

That's what I expected, however, Sep, Oct & Nov dates turned Red as well

Tom