=IF(ISBLANK(A1), "", IF(ISERR(DATEVALUE(A1)), TEXT(A1, "mm/dd/yyyy"), A1))
Frequently I am sent an Excel file full of data. It is almost guaranteed to have a date column. And almost always this column has a mix of Excel Dates, text, and blank entries.
The file looks fine, but at my level looks don't matter. I am trying to manipulate the data. Internally Excel sees dates and as an integer. This integer represents "so many units since some date" and is pretty useless when imported in to a database or converted to a CSV file.
I need to convert this date to a text value. Some of the value only appear as dates but are really text strings (not integers) and so the function has to ignore those.