Forum Discussion
Date format conversion USA / UK
I am in the Uk and enter a date in USA format into a cell of type ‘general’ e.g. for 11-FEB-2017 I enter 02/11/2017. I save the file and email it to USA. When opened in the USA excel has changed the f...
Hi Barbara,
First and foremost, you have to know that the date format in Excel is based on the date setting in Control Panel of your OS.
I think that the date format in your OS is: (dd/mm/yyyy), since you live in the UK.
So, you have to write the dates in this format or use the universal method: (yyyy/mm/dd) which converts the date to your local date setting whatever its format.
Don't use the USA format, because your local date setting isn't based on that format.
When you write 02/11/2017 and your local OS date setting is: (dd/mm/yyyy), that means 02/Nov/2017.
- Thanks so much for the answer been hitting a wall re this issue. You just solved the issue I've been having!
- Thanks Haytham
Is there a cell format that I can use which will just accept whatever I input and not try to recognise it as a date? Eg it was formatted as general, but excel still tries to convert it to a date. What if I use ‘text’ format?
The problem is I have to input it in USA format (despite being in the UK) because the system I am getting the date from is in USA format
Thanks!Hi,
My recommendations for you:
#1 Don't store the dates in text format!
#2 Continue to enter the dates according to your local format: (dd/mm/yyyy).
Please note that the Excel workbook that contains that dates, when you send it to someone in the US, the dates won't remain in your local format: (dd/mm/yyyy), It will change to the US format (M/d/yyyy), and vice versa.
The formatting changes, but the value is the same.
To make it less confusing, follow these steps to unify the formatting:
Step 1
Open Control Panel
Step 2
Click Region and Language
Step 3
Select English (United States) Format, and make sure that the date format is: (M/d/yyyy), then click OK.
After that, you'll notice that the dates format you already have will automatically change to US format.
And you can now use the US date format: (M/d/yyyy) to enter the dates in your workbooks.
All very good except it does not work when you are uploading files to a web server expecting the date is a given format.
This issue is decades old and just never properly dealt with. Even setting the Custom format for a column will not work as after saving and reopening a file the custom formatting is gone again which is why the uploaded file then gets rejected as having the wrong format for the date.
Really not acceptable this basic issue has never been properly addressed.
