Convert Date-as-Text

F

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
 
F

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
 
F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top