Conditional Formatting statement

**thomas.szwed.uk** · 08-28-2008, 09:11 AM

Hi there i am trying to write a statement for some conditional formatting.

I need to say

IF CellA1 is not in date format dd/mm/yyyy then TRUE otherwise FALSE

I have this but is not working so far. Can anyone help?

=IF(NOT(TEXT,dd/mm/yyyy),TRUE,FALSE)

Many Thanks.....

**dominicb** · 08-28-2008, 09:31 AM

Good afternoon thomas.szwed.uk

Originally Posted by thomas.szwed.uk

IF CellA1 is not in date format dd/mm/yyyy then TRUE otherwise FALSE

You can use this formula :
=CELL("format",A1)="D1"
However, because of the way Excel formulae handle formats you must use recalculate (F9) after the cell format has changed before the conditional format will reflect the changes.

HTH

DominicB

**thomas.szwed.uk** · 08-28-2008, 09:35 AM

No i wont be using this formula in the cell, i will be using it in the conditional statement. so will this still work?

**dominicb** · 08-28-2008, 09:59 AM

It's a conditional formatting formulae, like you asked for, but everything I said about recalculation still stands.

DominicB

**thomas.szwed.uk** · 08-28-2008, 10:02 AM

Well that renders it useless then as conditional formatting is meant to be automatic!!!! Thanks anyway. does anyone else have a better forumla??

**dominicb** · 08-28-2008, 10:23 AM

Hi thomas.szwed.uk

Conditional formatting is automatic, but the formula I provided is the only one that will do what is required, but Excel has a well documented hole where cell formats and formulae meet, and your requirement drops straight down that hole, ergo no-one else will "have a better forumla".

Excel does not consider changing a cell's color to be significant to calculation, and therefore will not necessarily recalculate a formula when a cell color is changed.

Source : http://www.cpearson.com/Excel/colors.aspx

Your only solution will be to resort to VBA.

HTH

DominicB