Curious problem when reading dates from text file to sheet

GingerStepChild

New Member
Joined
Oct 27, 2016
Messages
28
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top