Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Custom number formats - international (2003)

    If I use the NumberFormat "d-mmm-yyyy", then it works fine for English-speaking regional settings, but in Mexico, say, the "yyyy" component would need to be replaced with "aaaa" in order to work. Is there a way to specify a number format that will work no matter what the regional setting? I've seen [$-409] as a prefix sometimes... Is that how to do it?

    TIA

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

    Re: Custom number formats - international (2003)

    The [$$-409] prefix indicates US currency.

    The first two entries in the list of date formats are marked with an asterisk *. These formats will automatically be converted to the system date format of whoever opens the workbook. All other date formats, including custom formats, suffer from the problem you mention. This is one of the features of Excel where support for international use fails, alas.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Custom number formats - international (2003)

    Thanks for the quick reply. I had noticed the asterisks and wondered if they were related...
    The formatting is actually being applied in VBA, so I'm looking to format a whole column with something like:
    Columns("L:L").NumberFormat = "d-mmm-yyyy hh:mm_)"
    Do you think it's not possible?

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

    Re: Custom number formats - international (2003)

    That shouldn't be a problem; with a small number of exceptions, VBA uses US English settings. If you specify a date format the way you do, it should be translated to the local version automatically.

Posting Permissions

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