Convert Date-as-Text

[Fred Holmes]

Using Excel 2000

I have an Excel worksheet (.xls) that was produced by an accountant
exporting data from a particular accounting package. There is a colum
of dates of the m/d/yyyy format that don't sort as dates. They sort
alphabetically, which is of no use.

I'm looking for the VBA function that will convert text in the
m/d/yyyy format to an Excel date number.

Cells(n, m).formula = DateConverter(Cells(n, m).value)

or some such

And I will then write a loop macro to process each cell in the
column/range to convert the cell contents to an Excel date of the
usual kind that will sort properly.

A search in the Excel VBA help on "date" doesn't yield anything
interesting, and I can't think of a good search keyword for this.

I have no idea how this circumstance occurred, but I don't want to
bother the accountant about it. I have downloaded a lot of banking
and credit card stuff in .csv file format, opened it in Excel, and
saved it as an .xls and have always (usually?) gotten proper Excel
dates to appear in the cells in the date column.

Many thanks,

Fred Holms

 

[Dennis Tucker]

Does the normal "Format Cells..." not work?

 

[Fred Holmes]

Formatting the cells as date, e.g., mm/dd/yyyy doesn't make the cells
sort properly and doesn't change the way the dates are displayed.
They appear to be fixed text.

Fred Holmes

 

[Fred Holmes]

While formatting the cells didn't work directly, I thought about it
some more after your question, and the following worked:

cells(n, m).formula = cells(n, m).value

After processing the column with the above, formatting does work and
stuff sorts.

Fred Holmes