override locale computer settings

[Guest]

I appreciate any help. This is a problem I posted yesterday probably without
being very clear.

How can I override the Windows Regional settings so that my Excel File looks
the same in a computer in Italy and in the Netherlands without forcing the
user to change the Locale Regional settings?
This affects the Date and the Time format, specially within formulas.

Also it seems that the locale setting on Cell Format only works if the
locale is NOT the default.
i.e. A file with the default in UK Eng. and a cell date in Italian and
another in UK Eng, will have the cell in Italian on a Dutch computer but the
UK one will be turned into Dutch.

Besides, this setting does not work within a cell.
i.e. the formula TEXT(E12,"*yymmdd")&"-Blabla" will look like 050216-Blabla
on a UK Eng, but will look yy0216-Blabla on a Dutch or Italian computer

I appreciate any help. This is a problem posted yesterda

 

[Jim Rech]

You should avoid like the plague any functions that use region-specific
strings like TEXT. Otherwise you'll have to find a way of determining
dynamically the format codes you need in the region of the user.

One way is with a macro. Just as an example, this macro returns the three
date codes of the current region:

Sub ListDateCode()
Range("A1").Value = Application.International(xlYearCode)
Range("A2").Value = Application.International(xlMonthCode)
Range("A3").Value = Application.International(xlDayCode)
End Sub

Then you could refer to these results like this:
=TEXT(F2,REPT(A2,2)&"."&REPT(A3,2)&"."&REPT(A1,4))

--
Jim Rech
Excel MVP
|I appreciate any help. This is a problem I posted yesterday probably
without
| being very clear.
|
| How can I override the Windows Regional settings so that my Excel File
looks
| the same in a computer in Italy and in the Netherlands without forcing the
| user to change the Locale Regional settings?
| This affects the Date and the Time format, specially within formulas.
|
| Also it seems that the locale setting on Cell Format only works if the
| locale is NOT the default.
| i.e. A file with the default in UK Eng. and a cell date in Italian and
| another in UK Eng, will have the cell in Italian on a Dutch computer but
the
| UK one will be turned into Dutch.
|
| Besides, this setting does not work within a cell.
| i.e. the formula TEXT(E12,"*yymmdd")&"-Blabla" will look like
050216-Blabla
| on a UK Eng, but will look yy0216-Blabla on a Dutch or Italian computer
|
| I appreciate any help. This is a problem posted yesterda

 

[Guest]

Hi Jim
Thanks for your answer.
Unfortunately I am not familiar with macros. I would appreciate if you could
direct me a bit on this.
However, from your answer I think you misunderstood (or I misunderstood your
reply): I do not want to have the the format of the region of the user, quite
the opposite I want to override them in order to have the same format
independently of the region odf the user.

 

[Dave Peterson]

For me (USA settings), Jim's code put:

y
m
d

In A1:A3

Then Jim's formula:
=TEXT(F2,REPT(A2,2)&"."&REPT(A3,2)&"."&REPT(A1,4))
uses those codes to build the text string

So Rept(A2,2) will look like: mm
Rept(A3,2) will look like: dd
Rept(A1,4) will look like: yyyy

or if I substituted (manually), I'd get:
=TEXT(F2,"mm.dd.yyyy")

=======
So you could make that macro run each time you open the workbook--just name it
auto_open().

If you put those strings on a separate worksheet (hidden?????), you can use them
in your formula:

=TEXT(F2,REPT(Sheet2!A2,2)&"."&REPT(Sheet2!A3,2)&"."&REPT(Sheet2!A1,4))

(I used Sheet2 as my hidden worksheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Make sure you have a worksheet named Sheet2. Then close (and save) that
workbook and reopen it.

Now try the variation of Jim's worksheet formula as a test.