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

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



"Fred Holmes" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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

On Sat, 13 Mar 2010 15:10:38 -0700, "Dennis Tucker"
<(E-Mail Removed)> wrote:

>Does the normal "Format Cells..." not work?
>
>
>
>"Fred Holmes" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 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

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

On Sat, 13 Mar 2010 15:10:38 -0700, "Dennis Tucker"
<(E-Mail Removed)> wrote:

>Does the normal "Format Cells..." not work?
>
>
>
>"Fred Holmes" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 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