Excel Date Bug

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,299
Office Version
  1. 2010
A bug or different regional settings ?
 

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
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

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

Excel likes USA dates (who knew)
 

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
OK, but why does not each row have the same issue then?

Capture1.PNG
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,963
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 23, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,963
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 23, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
Yes, you are right. VBA will probably be the best solution I guess
 

Forum statistics

Threads
1,141,204
Messages
5,704,949
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top