Excel Date Bug

[Brutusar]

Hi, there seems to be a bug in Excel that during some special circumstances changes the format of dates when pasted into Excel from different software. What happens is that dates where the day is <=12, for instance, 04.05.2020 will be turned around to 05.04.2020. That does for course create some problems.

All dates which are correct has this formatting dd/mm/yyyy 00:00:00, and all have a date higher than 12. All dates which are turned around for some reason have this formatting dd/mm/yyyy 00:00, and are <=12. So all are under 12, and misses the seconds in the time formatting.

As I understand this bug was only there in the 2013 version, but I have the same issue in Office Excel 365 so I guess it is still there.

Does anybody has a suggestion for a simple workaround?

 

[arthurbr]

A bug or different regional settings ?

 

[Brutusar]

The problem is not it the regional settings. Searching a number of forums I have found several others who has the same issue, but that is all related to Excel 2013, I have not found anything about 365. I can literally see the date format is changed after it is pasted into Excel, and that only happens with dates where the day is <= 12. If the date is the 13th or higher, it does not happen.

 

[gaz_chops]

It sounds to me that dates are being imported from different regions in dif formats e.g. USA to UK, this is not a bug & happens in all versions of Excel.

 

[mole999]

Excel likes USA dates (who knew)

 

[Brutusar]

OK, but why does not each row have the same issue then?

This shows the data after pasting it in. The first row was pasted in as 01/06/2020, and the third as 02/05/2020. Both have been turned around here. These are formatted as dates, however, the two other are formatted as text, but they are all pasted from the same source in the same moment. When trying to format all rows as text, and then back to date, nothing happens that changes the issue. As I have a total of 50 columns, and 8 of them are dates, I would like to avoid too complicated solutions.

 

[Joe4]

The ones it "thinks" are dates, it puts (converts) in date format (right-justified).
The ones that it cannot convert, it leaves as text (as there is no 18th or 16th month of the year).

What file format is this file that you are opening in Excel?

 

[Brutusar]

I am scraping a website using Python. The data is saved in a data table, the different columns are saved into lists, and then pasted into Excel. It is not possible to import the table directly

 

[Joe4]

The issue is the format of the date and how Excel is expecting the date format to be in different (one is mm/dd/yyyy and the other is dd/mm/yyyy).
If that field was being brought in via some sort of text file import, you could specify that exact format of the field.
However, since it is not, and since tyou probably don't want to mess around with your system's regional settings, you will probably need to convert those values via formulas or VBA.

 

[Brutusar]

Yes, you are right. VBA will probably be the best solution I guess