Problem with conditional formatting dates

[chrissmarlow]

Hi Guys,

Hope you can help because this is driving me mad!

I have a spreadsheet with 7 dates in it (listed below), from cell A1:A7, in cell C1 I have the formula =EOMONTH(TODAY(),0)+1 which at present is 01/08/2011, to give me the first day of the next month.

I want to conditionally format the 7 dates (A1:A7) so that if they occur after the first day of the next month then they will highlight red. I have tried using the greater than formula in conditional formatting but this doesnt seem to work as it highlights "28/07/2011". All cells with data in them are formatted as short date (dd/mm/yyyy).

Can anyone help me out with this?

Dates in cells A1:A7
15/09/2011
29/09/2011
07/05/2011
29/09/2011
15/09/2011
15/09/2011
28/07/2011

 

[VoG]

Try the CF formula

=A1>C1

 

[chrissmarlow]

Try the CF formula

=A1>C1

I'v tried that and it still throws up the anomaly of 28/07/2011 being highlighted! Driving me nuts!

 

[VoG]

Sorry, I missed out a $

Excel Workbook
	A	B	C
1	15/09/2011		01/08/2011
2	29/09/2011
3	07/05/2011
4	29/09/2011
5	15/09/2011
6	15/09/2011
7	28/07/2011
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =A1>C$1	Abc

 

[chrissmarlow]

Sorry, I missed out a $

Excel Workbook
	A	B	C
1	15/09/2011		01/08/2011
2	29/09/2011
3	07/05/2011
4	29/09/2011
5	15/09/2011
6	15/09/2011
7	28/07/2011
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =A1>C$1	Abc

Thanks very much, iv tried this in a new sheet and typed all the dates out etc and it works! However, if I got back to my original sheet and put in the same conditional format formula then it still doesnt work??? I can only think its something to do with the data, I have formatted it as short date though so I dont know what else to do, any ideas?

 

[VoG]

What does

=ISNUMBER(A7)

return?

 

[chrissmarlow]

What does

=ISNUMBER(A7)

return?

It returns "FALSE"

 

[VoG]

So Excel doesn't think it is a date.

Select A1:A7, Data > Text to Columns, click Next twice, tick Date and select DMY then click Finish. Does that fix it?

 

[chrissmarlow]

So Excel doesn't think it is a date.

Select A1:A7, Data > Text to Columns, click Next twice, tick Date and select DMY then click Finish. Does that fix it?

Yes, thanks very much! What could have caused this? Is doing the text to columns thing the only way to get round it?

 

[VoG]

I don't know what would have caused it.

Either Text to Columns or format the cells as General and type in the dates again.