Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a spreadsheet that is used in both the UK and France, and I'm having problems dealing with the regional differences in presenting numbers and dates.

    Specifically, I need to use the TEXT function to present some numbers and dates in particular formats.

    For example, =TEXT(DATE(ReportYear,ReportPeriod,1),"yyyy-mm-""01""Thh:mm:ssZ") (where ReportYear = 2009 and ReportPeriod = 9) returns the following:
    - UK = 2009-09-01T00:00:00Z
    - France = yyyy-09-01T00:00:00Z

    As another example, =TEXT(CostImpact,"0.00") (where CostImpact = -168.6470588) returns:
    - UK = -168.65
    - France = -1.69

    In both examples, the UK result is correct. I presume the differences are due to:
    1) "yyyy" not being recognised as a year format in France;
    2) the French using a comma instead of a period to indicate the decimal part of the number.

    I use a computer with UK regional settings, and if I open a spreadsheet created in France, I'll initially see the incorrect results, until I refresh the cells, then I'll see the correct results. For the date, I think I can just construct a formula to create the required string, but dealing with the number could be more tricky.

    Any ideas? Thanks in advance for any responses!

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What happens if you let the cell formatting take care of the way dates are displayed instead of using the TEXT function?

  3. #3
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unfortunately, I need the cells to contain text, rather than simply formatting it as text on the fly (for reasons I won't go into here). Luckily, I've found a solution that seems to work under both regional settings.

    For the date, I've used =CONCATENATE(ReportFYear,"-",TEXT(ReportFPeriod,"00"),"-01T00:00:00Z").

    For the number, I've used =FIXED(CostImpact,2,TRUE) which formats the number to 2 (or whatever) decimal places, then formats it as text.

Posting Permissions

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