Excel 2007 conditional format query

[rj2010]

I am trying to format a cell based on the date in another cell. I can do this with other text and numbers in cells but cannot get it to work with a date.
Basically I would like the cell to change colour (which I know how to do) based on the date that is entered in the target cell.
Any advice ?

[Old_Geezer]

I'm no expert but I was under the impression one had to have something specific in the cell in question before you can format conditionally. How do you format based on a different cell ?

[dr b]

This describes how to format one cell based on the value in another cell:

http://www.exceldiges...alue-of-another-cell/

I assume you can do this? So in the "formula" box type something like:

=a1<"9/8/2011".

This will format the cell you're in when a1 contains a date prior to 9/8/2011.

[rj2010]

Thanks dr b, the problem is I want to use a specific date as opposed to a date before or after the date in the formula. It should be easy but I just cant get it to work !

[Maydup]

Can you say what it is you want to do and we could try to write it.

Is it ....when this cell = a certain date then the other cell changes colour?

[rj2010]

Yes, As an example I want cell d6 to change colour if cell d5 contains 15/12/11.
I know how to change the colour using other text/numbers just cannot get it to work with dates.

[dr b]

Interesting - it works just fine on dates if you use <, but not for = or >. I will ponder.

[Maydup]

Dates are difficult little b*ggers and so much easier in later versions.

But try formatting the cells first to read dd/mm/yyyy

Then do format colour if d6=15/12/2011

I think the 4 digit year might just help

[rj2010]

Sorry no, tried that...

[dr b]

well, here's a clunky way to get it done - convert dates to their excel dates - e.g. today (12/8/2011) is day 40855 (if you "clear formats" from a cell containing today's date, that's the number that will remain), It's then easy to conditionally format cell b1 if cell a1 contains 40855 or any other Excel date.

[dr b]

A little more experimenting and I think that's what has to be done. In fact A1 can be formatted to look like a date but the conditional formatting of B1 has to refer to the date's serial number, not, e.g., "12/12/2011".

[Maydup]

yep, agree only way I can think to do it as it'll recognise the numbers or text better than the dates. Its the / in the date that confuses the issue as it means divide when following if=

[New Judge]

None of the conditional statements in Excel (any version) work on dates unless they are converted to serial numbers (where 1st Jan 1900 = 1). Maidup has identified the cause of the problem in that Excel uses the “/” delimiter in the date as an instruction to divide. So 14/12/11, for example, is calculated as 0.10606. That is why the conditional statements appear to work for “less than” conditions, but do not work for “equals” or “greater than”.

[Old_Geezer]

Thanks dr b.

[Old_Geezer]

I guess it is a new with 2007 thing, that is far too recent for me then :-) Newest I've ever used is 2003. Oh well.....

[ChuckFickens]

Others have got this worked out, but to clarify how to do it...

enter the date you want to use in a cell and press enter, goto format cell for that cell and change the format from date to general and the date you've just entered will turn to a number (from your example, 15/12/2011 will turn into 40892), make a note of this number and then you can change the format of the cell back to date.

then goto the cell you want to perform the conditional formatting on and use the formula =$A$1=40892 (assuming the date is in A1) and set the formatting to be what you want, click OK a few times and it should work.