NumberFormat not getting changing onFly changing the regional sett

[Dwipayan Das]

Hi Excel experts ,

Sorry for posting a new question over here. I am having a hard time with one
of a Excel related issue.

Requirement:

1) User pulls some data ( includes numeric,Date fields ) to excel when
he/she is with German OS settings.

Date show in following format: dd.mm.yyyy ( eg: 23.09.2010)

2) Now on the fly he/she changes the OS regional settings to En-US

3) Here our code again reformats those columns with current regional
settings and polish the data according to its regional settings.

4) In our case all numeric values are getiing changed correctly to the EN-US
format but the Date field remains the same in German culture.

I debug the code and i found the NumberFormat remains the same as per the
German culture (dd.mm.yyyy).Its not getting change to mm/dd/yyyy

Is this the expected behaviour in Excel object model ,where the numberformat
for a cell/column doesn't get change to latest culture .

Waiting for your reply.........

Thanks,

Dwipayan Das

 

[JLGWhiz]

In Excel help file, this note is at the bottom of the instructions for
changing the Country/Regional settings:

When you change the country/region, settings, such as the format for
displaying currency and date, may also change. Check all the tabs in the
Regional and Language Options or Regional Options dialog box to make sure
they are correct.

Two items caught my eye. "may also change" and "Check all the tabs in the
.... Options".
This tells me that there is no absolute conversion guanrantee when the
changes are made. The safe thing would be to have a macro that checks
everything and fixes it if needed.