Excel and Windows Regional settings
 

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
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Excel and Windows Regional settings
 

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




"Bony Pony" wrote in message ...
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
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Excel and Windows Regional settings
 

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 ..."


"Ron de Bruin" wrote:

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




"Bony Pony" wrote in message ...
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
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Excel and Windows Regional settings
 

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




"Bony Pony" wrote in message ...
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 ..."


"Ron de Bruin" wrote:

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




"Bony Pony" wrote in message
...
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
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Excel and Windows Regional settings
 

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
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"Ron de Bruin" wrote:


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




"Bony Pony" wrote in message ...
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 ..."


"Ron de Bruin" wrote:

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




"Bony Pony" wrote in message
...
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
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."