CAN'T CHANGE DATE FORMAT

[mergim]

Hello everyone,

For some reason I cannot change the date format for my values (see picture). The cells marked with yellow are in US format, and the green ones are in European format. I have tried everything to format the values, so I have one type of format (European). I have tried to change format via "Ctrl + 1", and then choosing the European format "DD/MM/YYYY), "Text to Columns", replace the "/" with "-", added a "0" before the date, e.g. 05/23/2022, and not 5/23/2022, still nothing works. It seems like there is a glitch. I have even tried with Power Query.

Does anyone know how to fix this ?

Thanks in advance!

F

 

[Akuini]

It looks like your data is entered as text instead of date.
Try this code:
If the middle number is > 12, then it will swap the day & month.
Data in col A.

Sub flipDate()
Dim x, tx As String
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    tx = Replace(r.Text, "-", "/")
    x = Split(tx, "/")
    If UBound(x) = 2 Then
        If CLng(x(1)) > 12 Then
            r.Value = DateSerial(x(2), x(0), x(1))
        Else
            r.Value = DateValue(r.Text)
        End If
    End If
Next
Application.ScreenUpdating = True

End Sub

Example:

Book1
	A
1	DATE
2	01/03/2023
3	1/23/2022
4	2-3-2013
5	2-20-2012
Sheet4

result:

Book1
	A
1	DATE
2	01/03/2023
3	23/01/2022
4	02/03/2013
5	20/02/2012
Sheet4

 

[mergim]

It looks like your data is entered as text instead of date.
Try this code:
If the middle number is > 12, then it will swap the day & month.
Data in col A.

Sub flipDate()
Dim x, tx As String
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    tx = Replace(r.Text, "-", "/")
    x = Split(tx, "/")
    If UBound(x) = 2 Then
        If CLng(x(1)) > 12 Then
            r.Value = DateSerial(x(2), x(0), x(1))
        Else
            r.Value = DateValue(r.Text)
        End If
    End If
Next
Application.ScreenUpdating = True

End Sub

Example:

Book1
	A
1	DATE
2	01/03/2023
3	1/23/2022
4	2-3-2013
5	2-20-2012
Sheet4

result:

Book1
	A
1	DATE
2	01/03/2023
3	23/01/2022
4	02/03/2013
5	20/02/2012
Sheet4

Hello,

The value is set as a date and not a text...
The code helps! Thanks a lot!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.