Locking regional date formatting in the spreadsheet to UK

[captpicar]

Hi

I am making a spreadsheet form for some of our guys to use. This spreadsheet will be used on numerous computers with different regional formats.

I am trying to make the data entry into some cells be "dd/mm/yy hh:mm" and it will display "dd/mmm/yy hh:mm". This works fine if the computer's regional settings are UK based. However, when used on a computer with US regional settings the data entry has to be "mm/dd/yy hh:mm" and it still show the required "dd/mmm/yy hh:mm".

What I am looking for is some way for the excel spreadsheet to ignore the computer's regional settings and use the specific settings in the spreadsheet.

I am aware that the it is a lot easier to change the regional settings on the computers in question. But this spreadsheet will be used upon computers not owned by us and therefore the respective owner's wouldn't like their settings to be changed, however short a time.

Any help would be greatly appreciated.

Many thanks

Keith

 

[pgc01]

Hi Keith
Welcome to the board

Instead of using regional format for the input, why not use the international date format

If you enter the datetime using the standard international format, like

2015-11-14 10:32

this will be understood everywhere,

in the U.K. you'll see in the cell 14/11/15
in the U.S. you'll see in the cell 11/14/15
in Germany you'll see in the cell 14.11.15

using the regional formats of the computer with the adequate cell formats.

Using the international format for input makes this very easy.

 

[captpicar]

Hi Keith
Welcome to the board

Instead of using regional format for the input, why not use the international date format

If you enter the datetime using the standard international format, like

2015-11-14 10:32

this will be understood everywhere,

in the U.K. you'll see in the cell 14/11/15
in the U.S. you'll see in the cell 11/14/15
in Germany you'll see in the cell 14.11.15

using the regional formats of the computer with the adequate cell formats.

Using the international format for input makes this very easy.

Thanks a lot for the above. Sorry for the delay in replay, was away offshore and very bad phone signal.

Have tried your suggestion and it works brilliantly, many thanks

Keith

 

[pgc01]

I'm glad it helped. Cheers!

 

[JoRos]

Hi - this is very similar to our problem, but we have a shared document with several contributors. The person at the end of the chain, having to use the information is confused as heck because the date formats are different depending on who entered it. Does this mean that anyone who is adding to the document must be on International settings? Also, where do I find this? I was unable to find it in the Control Panel settings. I don't see an option for setting to International. Many thanks for your assistance! Josette