Conditional Formatting query

  • Thread starter Thread starter Potaroo
  • Start date Start date
P

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

---
 
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.
 
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 ..
 
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?
 
Back
Top