General discussion


Excel 2000 macro changes date format

By trevor_fox ·
I would like to report a bug in Excel 2000.

If a csv file is opened using a VBA macro, it does not format the dates correctly:

I have a file 'ABC.csv' whose first column contains date in the format 'dd/mm/yyyy hh:mm'.

If I open the file inexcel by double-clicking it explorer or using File/Open menu in excel then the file opens with the correct date format in the first column.

However if I run the code:
Workbooks.Open Filename:="ABC.csv"

it opens the file with the first column having 'mm/dd/yyyy hh:mm' format. If I look at the format of these cells, it shows that its in the format 'dd/mm/yyyy hh:mm', even though its not. I cannot reformat the date column either.

This does not occur in excel 97.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel 2000 macro changes date format

by DKlippert In reply to Excel 2000 macro changes ...

The data is comeing in as Text. It probably is alligned to the left of the cell rather than the right. Do you have Field names in the original files. Excel may be using the first text label to define the data type. You can change it by entering 1 ina cell. Copy it, select the dates and go to Edit>Paste Special> Multiply.

Related Discussions

Related Forums