formatting issue

**ashw1984** · 01-23-2009, 05:02 AM

Hi there

having a problem with formatting
column E
this column contains dates(and times) when i open the csv file.
these date's are changed to five digit numbers if i manually edit the format.

but when i run the macro the highlighted line does not change this column(it does change every other column).
the cells in this column become text such as 21.10.2008 15:56.
which is then an issue as i need to filter them when they are number and ultimatly group them in a pivot table.

i recorded the macro from what i was doing and then ran it.
it does work when it is in the module for the vehicle event report.csv

but when run from the other file(as shown below) it encounters this problem.

please help
I usually fix my own code but this has really stumped me.

thanks
Ash

Please Login or Register  to view this content.

**Richard Schollar** · 01-23-2009, 05:32 AM

Hi

When you open a csv file from code, the dates are converted using the American date formatting of MDY. Any dates that can't be converted in this format (ie those where the M bit is greater than 12 (which will be a large proportion if you are using Europen style formats of DMY)) will not be converted to a date but will remain as text. This is why your number format does not affect them. You should thus be concerned that:

1. Some of your dates are reamining as text and are thus not susceptible to number format changes

and much more importantly:

2. those dates that have been recognised as dates on opening the file are more than likely incorrect now as Excel will have read them as MDY dates and not DMY.

I believe you can get around this by using a QueryTable object to import the data rather than just opening the file as Andy Pope recommends here:

http://www.excelforum.com/excel-prog...t-failure.html

Richard

formatting issue

LinkBack

Thread Tools

Rate This Thread

Display

formatting issue

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions