VBA - Regional setting inside Excel

Hi ,

Would you know a way to force the Regional setting date and time format only inside an excel file?

I have an excel file with date format: mm/dd/yy hh:mm format, but some person from our team have a USA regional setting and they have dd/mm/yyyy hh:mm and this is causing alot of of problem. Due to some applications on their laptop, they cannot change the regional setting.

Would you know if we can form the regional setting only inside this excel file?

Thanks for all your help.

Date-time-format.jpg
LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
The short answer is 'No'.

The purpose of regional settings is to have the data displayed in a format that each user readily understands. The data, such as a date, is stored in the same way however it is to be shown. The setting is not in the file, but is in each machine's operating system.

This design is intended to overcome regional differences. Precisely what difficulties are you experiencing?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
The problem that i have right now is that the excel file file has multiple columns with data time format mm/dd/yy hh:mm. When users put dates in it, everything works fine but if a user open the file when he as the USA regional setting, the date gets wrong. Same thing if he put date and then, we open the file using Canada regional setting.

there is always a problem were it flips the month and day.

date-time-format-no-2.jpg
0
Ryan ChongCommented:
Number formatting to "Date" will based on the regional settings but if you selected the "Custom" setting, the format of the values should be retained.

can you also upload a sample and we will see if the formatting will be changed or not.

My local machine's date format is : M/d/yyyy
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Wilder1626Author Commented:
Sure. This is a sample only of the sheet with only A1 with the custom format mm/dd/yy hh:mm .

Please let me know if what format it gives you.

Thanks
mm-dd-yy-hh-mm-sample.xlsx
0
Ryan ChongCommented:
it looks good for me.

cell formatting
A1 cell number format
so it proves that the number formatting will be retained regardless of regional settings.
0
Wilder1626Author Commented:
Hi,
I see where the problem is. it is due to bad manipulation of the file.
Thanks for your help. At least I know that the regional setting cannot be by pass in excel, but the custom format is a good option.
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.