Monday, October 02, 2006

Convert Excel Dates to Text

Interesting bit:

=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.

No comments:

Post a Comment