Curious problem when reading dates from text file to sheet

[GingerStepChild]

Hi,

I have a year's worth of log files that i am reading into a single sheet. The data is tab separated. (I can't get it working using vbtab so use chr(9) instead).

Everything is working except that in each line there are two dates, one in the first column for when the alarm changes and one in the sixth column for a status change. When the script writes the first date to a cell it uses the US format (mm/dd/yyyy) while the sixth column is in RoW format (dd/mm/yyyy). In the text file both are in dd/mm/yyyy format.

In my sheet they are placed in columns B and G. Column B always defaults to US format. Column G always goes to RoW format.

Now when I change the format in vba with Sheet1.Columns("B:B").NumberFormat = "mm/dd/yyyy" it changes it to RoW format!!!

Country and region on the PC are set to Australia.

Can anyone explain why this happens? There is no formatting in the script except for the line above that I added. I've tried deleting the columns and sheet so everything is fresh.

Thanks

 

[GingerStepChild]

I've found out why the second value is written as a date in the right format. Its because it has a space appended at the end so is treated like a text rather than a date. Thank you Citect.

The first date doesn't have the space so is treated like a date, albeit wrongly.