Forum Discussion
Date format conversion USA / UK
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???