Conditional formatting cells to show no format if the cell contains a date

[xWiZardx]

Hi all, how can I condition format a cell to show no format if the cell contains a date

Please see the attached book1.xlsx for more info

Thanks for your help

Book1.xlsx

[etaf]

Try using
=CELL("format",D10)<>"D1"

see here

[TMS]

Use a formula instead of just checking the value in the cell.

=AND(D10<>"",LEFT(CELL("format",D10),1)="D",D10<=TODAY())

Regards, TMS

[TMS]

etaf's formula checks the specific date format. I was trying to generalise in case you change the format.


Regards, TMS

[xWiZardx]

Thank you TMS,

I tried changing the cell format to "Date" and using the formula you provided but the conditional formatting doesn't activate, can you please take a look?

Thanks a lot

Book2.xlsx

[TMS]

Try this, with a slight amendment to the CF formulae.

=AND(D10<>"",CELL("format",D10)<>"G",LEFT(CELL("format",D10),1)="D",D10<=TODAY())

=AND(D10<>"",CELL("format",D10)<>"G",LEFT(CELL("format",D10),1)="D",D10<=TODAY()+7)

=AND(D10<>"",CELL("format",D10)<>"G",LEFT(CELL("format",D10),1)="D",D10>TODAY())

I also copied and pasted the format from the first cell to the others ... but then made some General


Regards, TMS

[xWiZardx]

Hmm... still the same when I format the cell to "Date"

But thanks anyway, I'll just format those cells to "custom"

[TMS]

If you start with a cell that is formatted as General and type in a string that Excel recognises as a date, it will automatically change the format of the cell to Date and, typically, it wii copy the format from previous rows.


Regards, TMS