Excel and Windows Regional settings

[Bony Pony]

Hi everyone,

I need to distribute a spreadsheet to my colleagues across Europe and I have
run into an issue that I hope someone has a solution for.

Simply put, a formula like =text(a1,"dd-mmm-yyyy") works fine in a situation
where the Windows Regional settings are English. However, German or Swiss
whomever, call dd-mmm-yyyy something different.

Now Excel "translates" formulas based on the Regional settings but for some
reason, "dd-mmm-yyyy" is seen as a literal and therefore not translated so
the formula results in a #Value.

Short of making the user change their settings to suit the model
(unacceptable) how can I ensure this sort of thing does not happen?

Please note - the formula I am using is an example and not the only one.
Others include =info or =cell etc.

All help appreciated.

Many thanks!
Bony

 

[Ron de Bruin]

Hi Bony

I start with a page about this last week and this week we will add more things to the page.
But your problem is on the page already
http://www.rondebruin.nl/international.htm

 

[Bony Pony]

Hi Ron,
Thank you for your help as always.

I realise that the problem is much bigger than I thought. Fortunately, I
never use an = "False" or ="True" check so ok there. But the rest of it ....
sigh ...

One of my real problems is that In complicated sumproduct situations, I use
VBA to build the formula depending on the user choices and then paste the
formulas into a range. Clearly that is going to fail. Oh well - back to the
drawing board!

Thanks again!
Robert
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Hi Bony

I start with a page about this last week and this week we will add more things to the page.
But your problem is on the page already
http://www.rondebruin.nl/international.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Ron de Bruin]

Do you have the Text function working now ?

One of my real problems is that In complicated sumproduct situations, I use
VBA to build the formula depending on the user choices and then paste the
formulas into a range. Clearly that is going to fail. Oh well - back to the
drawing board!

This can work OK

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Ron,
Thank you for your help as always.

I realise that the problem is much bigger than I thought. Fortunately, I
never use an = "False" or ="True" check so ok there. But the rest of it ....
sigh ...

One of my real problems is that In complicated sumproduct situations, I use
VBA to build the formula depending on the user choices and then paste the
formulas into a range. Clearly that is going to fail. Oh well - back to the
drawing board!

Thanks again!
Robert

 

[Bony Pony]

Hi,
Yes - the Text thing has been working for a while now - I was using it as an
example.

I fixed it by using =date(year(now()),month(now()),0) and just formatted the
cell as a "dd-mmm-yyyy" custom date format.

I do prefer your way .....

Thanks again.
Robert