Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    US vs UK date formats (Office 2000 SR1)

    I have a question related to Michael Evans
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: US vs UK date formats (Office 2000 SR1)

    Here in The Netherlands it's even worse: we use the comma as decimal separator and period as thousands separator. This causes numeric values to be mucked up too... This has been a problem in all versions of Excel. Fortunately, there are settings for this in Excel now (Application.DecimalSeparator, Application.ThousandsSeparator and Application.UseSystemSeparators), but not for the date format.

    My guess is that while you are recording the macro, you are still executing the commands in the Excel interface, which takes your system date setting (and other regional settings) into account. When you run the macro, VBA is executing the commands, and VBA is 100% US oriented; if you enter a literal date in VBA code, it *must* be in mm/dd/yy format.

    I have never found a completely satisfactory solution. Switching the PC's to US format is unattractive, and modifying all csv files isn't tempting either...

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: US vs UK date formats (Office 2000 SR1)

    I do not have an answer but wanted to step in and help you flog your 'migration managers' for saying the transition from 97 to 2000 will be invisible. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> <img src=/S/bash.gif border=0 alt=bash width=35 height=39> <img src=/S/bash.gif border=0 alt=bash width=35 height=39> Unfortunately the literature you read really only concerns formulas not VBA (IMO). The only reason these people can possibly say that is because they have no practical experience creating applications (meaning using VBA) in Office. There are tons of undocumented differences between these versions (as well as 2002) that have caused me many fits of rage (and thereby increasing my already colorful vocabulary <img src=/S/devil.gif border=0 alt=devil width=15 height=15>). I always use the least-common denominator feature set, no add-ins, no extra references and I can guarantee that you will find problems if you write anything more than a simple application. Having to test forward/backward compatibility for three versions of Excel greatly increases the time it takes to develop a tool. I think I need to retire before my company forces Office 2003 on us.

    And yes, Office is US-centric as the other poster said so you'll have to explicitly force the date format. As for making these changes, you might consider writing code to edit the code in your other workbooks. I haven't done this myself but there are plenty of examples of how to do this (write VBA to edit VBA, that is).

    Deb

  4. #4
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    35
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: US vs UK date formats (Office 2000 SR1)

    Steve,
    I have also had lots of problems with Excel mangling dates and found a variety of solutions over the years. One rule I make now is to set up a custom date of dd/mmm/yyyy to ensure I get the month name. Screwups become easier to spot! I recently had a similar problem to yours whereby importing from CSV caused some dates to show as US whereas others were OK. If the day was 12 or less Excel automatically recognises it as a US date and inserts in the cell accordingly right justified (since it is treated as a number) e.g. "1/2/03" becomes 2/1/2003 (US) whereas "13/2/03" remains as 13/2/03! Pre formatting the cells as dates to the format you want (if custom date doesn't work try formatting the cells as text) before starting the import may help. If dates are read by the macro before being inserted in cells, a line something like 'TxDate = Format( MyDate, "dd/mmm/yy")' might work. I can probably find you some samples but it still looks like some editing for you.

    Best of luck

    DougieW

Posting Permissions

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