Conditional Formatting query
 

Hi all,
I need to place conditions on a spreadsheet dealing with dates.

In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc
Lets say they are in cells B3, B4 and B5 respectively.
At cell A1 there is a date =TODAY()

In the B column I have placed conditional formatting as

Cell Value Is: Equal or greater than $A$1 - it returns a cell coloured RED

I'm at a loss to place a value in for those dates between Cell A and those
in Column B from day 1 to day 90 (ie within 3 months) to return a colour of
Yellow


Hope you can understand my query,
Cheers, Pete.

Conditional Formatting query
 

Select the range to be conditionally formatted,
eg select B3:B5 (with B3 active),
then apply CF using "Formula is" as follows:

Condition 1, Formula is:
=AND(B3<"",B3-$A$1<=0)
Format Red fill

Condition 2, Formula is:
=AND(B3<"",B3-$A$10,B3-$A$1<=90)
Format Yellow fill
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Potaroo" wrote in message
...
Hi all,
I need to place conditions on a spreadsheet dealing with dates.

In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc
Lets say they are in cells B3, B4 and B5 respectively.
At cell A1 there is a date =TODAY()

In the B column I have placed conditional formatting as

Cell Value Is: Equal or greater than $A$1 - it returns a cell coloured
RED

I'm at a loss to place a value in for those dates between Cell A and those
in Column B from day 1 to day 90 (ie within 3 months) to return a colour
of
Yellow


Hope you can understand my query,
Cheers, Pete.

Conditional Formatting query
 

well, guess you could/should always close off the traffic lights with a
condition 3 for green?

Condition 3, Formula is:
=AND(B3<"",B3-$A$190)
Format Green fill

The check: B3<""
ensures that blank cells will not trigger the CF
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Conditional Formatting query
 

Oh Max, it didn't work ;-(
Checked, made sure that the cells were formatted the same, ie "Number"
"Date" 08-Jun-07" etc, but to no avail.



"Max" wrote in message
...
Select the range to be conditionally formatted,
eg select B3:B5 (with B3 active),
then apply CF using "Formula is" as follows:

Condition 1, Formula is:
=AND(B3<"",B3-$A$1<=0)
Format Red fill

Condition 2, Formula is:
=AND(B3<"",B3-$A$10,B3-$A$1<=90)
Format Yellow fill
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Potaroo" wrote in message
...
Hi all,
I need to place conditions on a spreadsheet dealing with dates.

In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc
Lets say they are in cells B3, B4 and B5 respectively.
At cell A1 there is a date =TODAY()

In the B column I have placed conditional formatting as

Cell Value Is: Equal or greater than $A$1 - it returns a cell coloured
RED

I'm at a loss to place a value in for those dates between Cell A and
those
in Column B from day 1 to day 90 (ie within 3 months) to return a colour
of
Yellow


Hope you can understand my query,
Cheers, Pete.

Conditional Formatting query
 

No reason why it shouldn't work for you unless the source dates in B3:B5 are
not real dates recognized by Excel.

Note that formatting in itself doesn't convert text dates to real dates. You
can use Data Text to Columns to convert. Select the range of dates, eg
B3:B5, then click Data Text to Columns. Click Next Next. In step 3 of
the wiz., check Date under "Column data format", then select the appropriate
date format from the droplist, eg: DMY, and click Finish.

Here's a sample which illustrates the CF set-up (working, of course):
http://www.savefile.com/files/723204
CF_Dates_Traffic Lights.xls

Let me know here how it went for you ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Potaroo" wrote in message
...
Oh Max, it didn't work ;-(
Checked, made sure that the cells were formatted the same, ie "Number"
"Date" 08-Jun-07" etc, but to no avail.

Conditional Formatting query
 

Hi

You say the cells are formatted as Date, but was this before or after
data was entered?
What do you get if you enter a formula on the sheet =B3+1?

Max's formulae should work perfectly, what result are you seeing?

--
Regards

Roger Govier


"Potaroo" wrote in message
...
Oh Max, it didn't work ;-(
Checked, made sure that the cells were formatted the same, ie "Number"
"Date" 08-Jun-07" etc, but to no avail.



"Max" wrote in message
...
Select the range to be conditionally formatted,
eg select B3:B5 (with B3 active),
then apply CF using "Formula is" as follows:

Condition 1, Formula is:
=AND(B3<"",B3-$A$1<=0)
Format Red fill

Condition 2, Formula is:
=AND(B3<"",B3-$A$10,B3-$A$1<=90)
Format Yellow fill
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Potaroo" wrote in message
...
Hi all,
I need to place conditions on a spreadsheet dealing with dates.

In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc
Lets say they are in cells B3, B4 and B5 respectively.
At cell A1 there is a date =TODAY()

In the B column I have placed conditional formatting as

Cell Value Is: Equal or greater than $A$1 - it returns a cell
coloured
RED

I'm at a loss to place a value in for those dates between Cell A
and
those
in Column B from day 1 to day 90 (ie within 3 months) to return a
colour
of
Yellow


Hope you can understand my query,
Cheers, Pete.