Formatting / Formula
 

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

Formatting / Formula
 

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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=TODAY()-A1=90

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.


If this post helps click Yes
---------------
Jacob Skaria


"Telford Tom" wrote:

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

Formatting / Formula
 

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" wrote:

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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=TODAY()-A1=90

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.


If this post helps click Yes
---------------
Jacob Skaria


"Telford Tom" wrote:

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

Formatting / Formula
 

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
---------------
Jacob Skaria


"Telford Tom" wrote:

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" wrote:

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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=TODAY()-A1=90

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.


If this post helps click Yes
---------------
Jacob Skaria


"Telford Tom" wrote:

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

Formatting / Formula
 

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Telford Tom" wrote:

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" wrote:

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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=TODAY()-A1=90

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.


If this post helps click Yes
---------------
Jacob Skaria


"Telford Tom" wrote:

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

Formatting / Formula
 

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


Gord Dibben MS Excel MVP

On Sat, 21 Nov 2009 02:14:06 -0800, Telford Tom
wrote:

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" wrote:

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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=TODAY()-A1=90

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.


If this post helps click Yes
---------------
Jacob Skaria


"Telford Tom" wrote:

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

Formatting / Formula
 

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

Tom

"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP

On Sat, 21 Nov 2009 02:14:06 -0800, Telford Tom
wrote:

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" wrote:

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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=TODAY()-A1=90

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.


If this post helps click Yes
---------------
Jacob Skaria


"Telford Tom" wrote:

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



.