Curious problem when reading dates from text file to sheet
Results 1 to 2 of 2

Thread: Curious problem when reading dates from text file to sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Curious problem when reading dates from text file to sheet

    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

  2. #2
    New Member
    Join Date
    Oct 2016
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Curious problem when reading dates from text file to sheet

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •