Conditional Formatting query

[Potaroo]

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.

 

[Max]

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$1>0,B3-$A$1<=90)
Format > Yellow fill

 

[Max]

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$1>90)
Format > Green fill

The check: B3<>""
ensures that blank cells will not trigger the CF

---

 

[Potaroo]

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]

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 ..

 

[Roger Govier]

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?