day/month swapping 1

[rainbowrabbit]

I have a macro that opens a series of text files and puts them into an Excel spreadsheet.

When doing so it swaps the month day around as follows;

07/01/1998 becomes 01/07/1998

etc .

Dates like 25/08/2000 remain unaffected

When doing the same process manually the date formats are fine, but in code (I am using the opentext method) the day and month swap places.

Is there a way to remedy this or do I need to write a routine to change all the incorrect dates in the code??

 

[PaulTEG]

Regional settings in the control panel should allow you to control this
--Paul

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[afryer]

Hi,

In addition to what was said below you could also try right clicking on the cells you wish to have the date on, select the number tab, followed by date field and ensure that the field type is DD/MM/YYYY.

This is quite a common error across Microsoft/VB/VBA applicatons and is caused because the default for dates seems to be the american style MM/DD/YYYY.

In addition if you wished to do a bit of module coding then you could look at the Format function as this allows you to specify the format of your dates manually.

Hope this helps

Andrew

 

[afryer]

Hi,

Just as another thought - if that doesn't work then format the cells again, select custom and it the type text box enter dd/mm/yyyy

Andrew

 

[xlbo]

Formatting will do nothing to the dates once they have been imported incorrectly. regional settings may help but your best bet is to record yourself importing a file, going through the text import wizard. Within the wizard, you can set the type on a date field. Once you have the recorded code, you can then apply it to your macro

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[rainbowrabbit]

Thanks for your input on this issue which is now have finally resolved!

Firstly I tried to record a macro that ran through importing 1 file where during the import I specify the type and format of data being imported into certain date fields. This worked fine when I recorded it:

Workbooks.OpenText Filename:= _
"P:\M.I.T\Projects\Excellence in Cities\EIB Data\4_2003 Autumn\TestApp\Raw Files\BeaGT11.MTF" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _
(1, 1), Array(2, 1))

however when this is run via code the dates still get messed up! It took me a while but then I discovered that if you specify fieldinfo arrays, you cannot pick and choose, you must specify ALL columns being imported;

Workbooks.OpenText Filename:=dlgFiles(intcounter), Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:= _
Array(Array(1, 2), Array(2, 2), Array(3, 2), _
Array(4, 2), Array(5, 2), Array(6, 2), _
Array(7, 2), Array(8, 4), Array(9, 2), Array(10, 2), _
Array(11, 4), Array(12, 2), Array(13, 2))

Here columns 8 and 11 contain dates so they have the number 4 in the second part of the array. The number 2 is default general. If you only have;

array(array(8,4),array(11,4))

in the fieldinfo parameter it will NOT work!!

With all columns defined the dates remain untouched...

 

[SkipVought]

rainbowrabbit,

If you were to select ONE of your messed up dates and FORMAT that cell (FOrmat/Cells - Number Tab...

what is the Selected Category?

If the Selected Category is NOT General, SELECT GENERAL and tell us what happens.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[rainbowrabbit]

skip,

This is so bizarre!!

When I run it with messed up dates and select one of them the number format is custom "dd/mm/yyyy". I then change it to General and this gives me the number format 31280 or something.

When I change this back to a pre-defined date format the date displayed is correct i.e. a date initially showing 04/12/1998 becomes 12/04/1998 which is the correct date!!

Is something strange going on with the custom date format?

 

[rainbowrabbit]

apologies skip! I looked at this before my first coffee of the day!

The number format is set to custom "mm/dd/yyyy" however the day and month have been swapped, if you change to the Excel date formats it looks ok but that is the m/d/yy format!

Excel seems to interpret the data as it gets loaded in, if the day is higer than the 12th of the month it swaps if not it leaves them??