Microsoft Excel - identifying false dates

Question

Thw following data is in Date Format (UK, DMY):

09/06/1995
12/32/1997
03/14/1875
12/03/1976
05/12/1976

In the column next to this data I would like to highlight which dates have errors. If I use =ISNONTEXT I get:

09/06/1995	TRUE
12/32/1997	FALSE
03/14/1875	FALSE
12/03/1976	TRUE
05/12/1976	TRUE

But the third row, 14/03/1875, should be TRUE, but it just needs converting from USA MDY format to UK DMY. So using =ISNONTEXT is not a suitable formula.

How do I identify any incorrect fields and then reformat all into DMY. 03/14/1875 will not reformat using text to columns fuction.

Answer 1

Excel cannot handle dates before the year 1900, so it treats 03/14/1875 as text, and 14/03/1875 as well.