Text Function with Different Regions

Topic

New Reply

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!

Reply | Quote

Replies

What happens if you let the cell formatting take care of the way dates are displayed instead of using the TEXT function?

Reply | Quote

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.

Reply | Quote