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...
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!
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.
slipperxI agree. This issue is a NIGHTMARE and the pleas online to get a proper solution in place are endless.
I just stumbled over this when working with SQL Data. I have Dates stored as TEXT in a PostgreSQL DB (which I wrote into the table from a different workbook) and while the Strings in the SQL table all follow the format "DD/MM/YYYY" , when I print the query result to my other workbook, excel suddenly parses the dates according to "MM/DD/YYYY" DESPITE my region setting being UK.
I.e.
Data2(1,1) = "01/08/2022" is shown in my locals window and also printed as such using the Debug.Print(Data2(1,1)) method.
Now If I copy that string and paste it into my sheet, I get the correct converted date value for 1st Aug 2022.
YET doing the following
PasteRange.Resize(UBound(Data2, 1), UBound(Data2, 2)) = Data2
suddenly parses the dates as if my settings were US ("MM/DD/YYYY")
I mean what on earth is going on here???
