Date formatting issue

[CookieMonster76]

Hi

I have a text file that comes out of a finance system, that needs converting into excel.

It has a series of dates. I have recorded a macro, and the result comes out as I would expect whilst recording, but when I re-run it as a macro, it is changing the format i.e. (English date format) 04/10/22 is changing to 10/04/22.

The odd thing is that it is only doing it one some lines, the vast majority are correct.

The relevant part of the code is:

DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(11, 1), Array(21, 1), Array(22, 1)

Does anyone have a suggestion as to why, and how I can fix it?

Thanks
Paul

 

[cadandcode]

Sub Maybe()
Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(2)
c.Offset(, 2).Value = "'" & Format(c, "MM.DD.YYYY")
Next c
End Sub

 

[CookieMonster76]

Thanks

However, this also changes the format of the ones that do work - i.e. It's converting 21/12/2022 to 12.21.2022

 

[cadandcode]

Try to change dots in "MM.DD.YYYY" to "MM/DD/YYYY"

 

[Alex Blakenburg]

I have shut down for the night, but in your original code, does it let you add ", Local:=True" (without quotes but including the comma) at the end of the line ?

 

[cadandcode]

I have shut down for the night, but in your original code, does it let you add ", Local:=True" (without quotes but including the comma) at the end of the line ?

It is about local? , for countries and ; for some

 

[Alex Blakenburg]

I don't understand your message, did you try

DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(11, 1), Array(21, 1), Array(22, 1)),Local:=True

 

[cadandcode]

I don't understand your message, did you try

DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(11, 1), Array(21, 1), Array(22, 1)),Local:=True

No, I did not. Im not owner of question tho.

 

[CookieMonster76]

Thanks. At first glance that appears to have done the trick.

 

[Alex Blakenburg]

Hopefully it continues to do the trick . Let us know if that changes.