Mixed Date Formats (DD/MM/YY and MM/DD/YY) in same Column

[AfuaImade]

Hello,

- I have a spreadsheet with loads of rows
- In column C, I have dates with mixed formats (DD/MM/YY and MM/DD/YY)
- Also some of these dates are showing as text(i.e to the left of the cell
P.S The dates are mixed format, I dont need to change them all, which is what will happen. I'll just end up with the same problem reversed, if I use your suggestion. Thanks.


Any help to get them all to dd/mm/yy will be appreciated.

 

[MARK858]

Re: Mixed Date Formats in the same Column

Hi Mark,

So I've changed the date settings on my computer and that leaves me with one issue now.

- Converting the dates recognized as texts to dates.

If you have changed your windows regional setting to dd/mm/yyyy (personally I wouldn't have done that but each to their own) then the instructions in post number 12 should work if the formula posted by steve the fish doesn't work for you (I haven't tested the formula).

 

[Akuini]

Re: Mixed Date Formats in the same Column

Try this:

I'm using "dd/mmm/yy h:mm" so that it will display the name of the month so you can easily check whether the results are correct.

Sub a1105914a()
'https://www.mrexcel.com/forum/excel-questions/1105914-mixed-date-formats-dd-mm-yy-mm-dd-yy-same-column.html
Dim va, x
Dim i As Long, c As Range, z As String
With Range("C2", Cells(Rows.count, "C").End(xlUp))
va = .Value
For i = 1 To UBound(va, 1)
    x = va(i, 1)
    z = CStr(Format(x, "Short Time"))
    If Left(x, 2) < 13 Then
    va(i, 1) = Format(DateSerial(Year(x), Day(x), Month(x)), "dd-mm-yy") & " " & z
    Else
    va(i, 1) = CDate(x)
    End If
Next
.Value = va
.NumberFormat = "dd/mmm/yy h:mm"
.HorizontalAlignment = xlRight
End With
End Sub

Note: my PC regional setting is "dd-mm-yy", if yours is "mm-dd-yy" then I'm not sure it will affect the code or not.

 

[AfuaImade]

Re: Mixed Date Formats in the same Column

This worked! Thank you so much

 

[Akuini]

Re: Mixed Date Formats in the same Column

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

 

[AfuaImade]

Re: Mixed Date Formats in the same Column

Hi Akuini

So I realized that my data viz tool is not recognizing the properly formatted dates as date (cos they are to the left of the cell instead of right)

Can you advise how to get them to the right of the cell?

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

 

[Akuini]

Re: Mixed Date Formats in the same Column

So I realized that my data viz tool is not recognizing the properly formatted dates as date (cos they are to the left of the cell instead of right)

I don't understand.
After you run the macro all data in column C is date & aligned to the right.