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?

 

[Joe4]

OK, assuming the ones it converted as dates have month and day backwards, try this code and see if that fixes it for you.
Note that you will need to set column and starting row in the code, and it should then be able to figure out the rest.

Sub FixDates()

    Dim col As String
    Dim start_row As Long
    Dim end_row As Long
    Dim r As Long
    
'   Enter which column to apply this to
    col = "A"
    
'   Enter first row that data starts on
    start_row = 2

    Application.ScreenUpdating = False

'   Dynamically find last row in column with data
    end_row = Cells(Rows.Count, col).End(xlUp).Row
    
'   Loop through each cell in range and fix date
    For r = start_row To end_row
'       If entry is already a date, switch months and columns
        If IsDate(Cells(r, col)) Then
            Cells(r, col) = DateValue(Day(Cells(r, col)) & "/" & Month(Cells(r, col)) & "/" & Year(Cells(r, col)))
'       If it is text, switch months and columns
        Else
            Cells(r, col) = DateValue(Mid(Cells(r, col), 3, 2) & "/" & Left(Cells(r, col), 2) & "/" & Right(Cells(r, col), 4))
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[Brutusar]

Thank you very much. The code works for changing text/date format, but no values (day/month) are changed. See "before-after" screenshots below. (First screenshot is the values as pasted, second screenshot after your code have been run.

On the first row correct date should be 03/06/2020
Second row 02/06/2020
Fourth row 07/05/2020
Fifth row 02/05/2020
And last row 10/03/2020

 

[Joe4]

Are you using an American version of Excel or European?

Try changing this line:

Cells(r, col) = DateValue(Day(Cells(r, col)) & "/" & Month(Cells(r, col)) & "/" & Year(Cells(r, col)))

to this:

Cells(r, col) = DateSerial(Year(Cells(r, col)),Day(Cells(r, col)),Month(Cells(r, col)))

If that doesn't work, try this line instead:

Cells(r, col) = DateSerial(Year(Cells(r, col)),Month(Cells(r, col)),Day(Cells(r, col)))

 

[Brutusar]

Hi,

I tried both changes, but nothing is happening at all with either one. I am on European Excel.

 

[Joe4]

I tried both changes, but nothing is happening at all with either one. I am on European Excel.

I am using an American, so unfortunately, I cannot replicate your environment and debug what is happening. Maybe someone else you uses the European version can weigh in.

 

[Brutusar]

OK, thank you very much for your time and effort!

 

[B___P]

Hi Brutusaur,
as european user I often face this issue.
Tried many solutions but the one I prefer is

Cells(r, col).formulalocal= yourvalue

I think an inplace substitution is difficult because that cell learned to have american format and tries to keep it.
You should use the trick at import time
Hope this helps

 

[Brutusar]

OK, will try that out. Thank you very much for the tip and code!!