Hardware

Ask something
Discussion Locked

Excel 2000 macro changes date format

0 helpful
1 reply

by tipscomp

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

1 reply

by dklippert

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.

This discussion is closed to new replies.