Date format conversion USA / UK

[Barbara Floyd]

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 format of the cell to ‘date’ and has Interchanged the month and day. So it reads as 11/02/2017 which is interpretered in America as 02-Nov-2017. How can I stop this from happening? Note that the spreadsheet is sent to multiple people so I can’t rely on changing local settings on my Excel, it has to be something in the spreadsheet itself which forces what was Input to be displayed. Thanks in advance for any help!

[#2]

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.

[#3]

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!

[#4]

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.

[#5]

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.

[#6]

@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???

[#7]

Thanks so much for the answer been hitting a wall re this issue. You just solved the issue I've been having!