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.

LVL 11
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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?

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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
Wilder1626Author Commented:
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.
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.