How do I highlight cells that do not contain valid dates.

**AndyRoo119** · 11-09-2015, 08:46 AM

Hi All,
I have a series of sheets contain dates of when employee training is either planned (blue), current (green), within 2 months of being out of date (amber) or out of date (red).
I use the formula function within conditional formatting to colour the relevant cells, relative to the date entered in them.
For example, to display cells shaded blue to show that training is planned I use the formula "=AND(C6<>"",C6>TODAY().
I then have this formula applied to cells to $C$6:$M$48 and this all works perfectly well.

What I would like to know is; Is there a way, using a conditional formatting formula, to highlight cells when anything is entered into the cells that is not a valid date?

Regards

Drew

**Obsessed** · 11-09-2015, 08:54 AM

I'd just use data validation to force a date to be entered.

**Glenn Kennedy** · 11-09-2015, 08:58 AM

This returns TRUE if a cell contains a valid date:

=AND(ISNUMBER(A1),LEFT(CELL("format",A1),1)="D")

so, you could try this:

=NOT(AND(ISNUMBER(A1),LEFT(CELL("format",A1),1)="D"))

**Shareez Saleem** · 11-09-2015, 09:04 AM

=$a1=text($a1,"ddd")

How do I highlight cells that do not contain valid dates.

LinkBack

Thread Tools

Rate This Thread

Display

How do I highlight cells that do not contain valid dates.

Re: How do I highlight cells that do not contain valid dates.

Re: How do I highlight cells that do not contain valid dates.

Re: How do I highlight cells that do not contain valid dates.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Highlight cells between two dates

[SOLVED] Help with Macro to highlight cells between dates

[SOLVED] Highlight Cells Based on Dates

[SOLVED] Trying to get Excel to highlight cells based on assigned dates.

Highlight item not in Valid List

[SOLVED] counting valid dates in a range of cells

automatically highlight cells with expired dates?!

Bookmarks

Bookmarks

Posting Permissions