Conditional Formatting Dates

[ExcelRoy]

Hi,

I need to implement some conditional formatting based on a formula, but i cannot sem to get the formula working correctly

I am trying to achieve greyed out text when the current "real" date passes a named range of date_1_to

i was thinking along the line of "=now() > date_1_to" but this doesn't work

any ideas?

Thanks

 

[Andrew Poulsom]

In what way doesn't that work?

 

[ExcelRoy]

Hi Andrew,

it doesn't perform the font colour change?

date_1_to = 05/09/11

so if the current date passes 05/09/11 then the font should change colour

Thanks

 

[Joe4]

Is "date_1_to" a named range? If not, how is it defined?

Is your date actually entered as a date or text?

 

[p45cal]

Works here:
<embed src="https://www.box.net/embed/psrcuuv6pcqrmud.swf" width="466" height="400" wmode="opaque" type="application/x-shockwave-flash" allowFullScreen="true" allowScriptAccess="always">
where the green cell is the named range date_1_to and the formatted cells are E6:E11
If the image doesn't appear above here's a link to it.

 

[ExcelRoy]

The only thing i can think is that the columns i need font colour change to "E-M" within this range are merged cells, headings etc

maybe they clash somehow

thanks

 

[ExcelRoy]

Hi Joe4

Yes the date is a named range and yes it is entered as a date

thanks

 

[p45cal]

I tried with some merged cells in the range and it worked.
I tried it on a table (list), including the headers and it worked.
Perhaps the date is not really a date in date_1_to but text.

To verify it's a date, select that cell date_1_to and right-click it and choose Format cells…, then on the Number tab, choose General as the format, and in the Sample box you should see a number such as 40792, perhaps with some decimals too - this confirms it's a date. If you still see what looks like a date then it's text.
Cancel out of the dialogue to avoid changing anything.

 

[ExcelRoy]

Hi guys,

got it working now

as P45cal has said it was the setting of the named range cell. it was set as text

Thanks very much guys