VBA: Converting US and UK date formats

[kungfauxn00b]

Hey, I regularly download a report which includes date formats in the following format: "mm/dd/yyyy hh:mm AM/PM" but as I'm based in the UK, Excel converts the dates it recognizes to "dd/mm/yyyy hh:mm". For example, here's an example with two dates; 11th and 19th September:

As you can see, Excel has converted the 11th September to 9th November. Is there a way to convert all text formats and date formats to the same format?

What I have so far:

Sub ConvertDatesInSelection()    
    With Selection
        .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
        .NumberFormat = "mm/dd/yyyy hh:mm"
    End With
End Sub

Which does this:

As you can see, as Excel has already converted 11th Sep to 9th Nov, it's just changing the format and not the actual date.

Any ideas greatly appreciated!!

 

[JLGWhiz]

maybe this link will help you.
https://www.extendoffice.com/documents/excel/4162-excel-convert-european-date-to-us-date.html

 

[MARK858]

Try in a blank column...

=IFERROR(IF(ISNUMBER(A1),VALUE(TEXT(A1,"mm/dd/yyyy")),DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,2))),DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,1)))

Change A1 to suit and drag down, copy/paste as values and format as a date.

 

[kungfauxn00b]

maybe this link will help you.
https://www.extendoffice.com/documents/excel/4162-excel-convert-european-date-to-us-date.html

Hey, thanks but no, not really. I don't need to change the format, I'm good with US format. My problem is the mixed format and the fact that Excel is changing the 11th Sep to 9th Nov when the sheet is opened.

 

[kungfauxn00b]

Try in a blank column...

Change A1 to suit and drag down, copy/paste as values and format as a date.

Thanks Mark, this changes the 9th Nov to 11th Sep but errors with the 19th Sep date. Also was looking for a VBA solution but I appreciate the formula work-around!

 

[MARK858]

Try the code below on a copy of your data, it assumes your dates are in column A from cell A2 down (I have assumed this as you haven't stated the range), if it is different then the code needs amending.

Sub Macro5()
    Dim X As String, Y As String

    X = "=IF(ISNUMBER(RC[1]),VALUE(TEXT(RC[1],""mm/dd/yyyy hh:mm"")),DATEVALUE(LEFT(TEXT(DATE(MID(RC[1],FIND(""/"",RC[1],FIND(""/"",RC[1])+1)+1,4),LEFT(RC[1],FIND(""/"",RC[1])-1),MID(RC[1],FIND(""/"",RC[1])+1,2)),""DD/MM/YYYY"")&"" ""&TRIM(RIGHT(RC[1],8)),10))"
    Y = "+TIMEVALUE(RIGHT(TEXT(DATE(MID(RC[1],FIND(""/"",RC[1],FIND(""/"",RC[1])+1)+1,4),LEFT(RC[1],FIND(""/"",RC[1])-1),MID(RC[1],FIND(""/"",RC[1])+1,2)),""DD/MM/YYYY"")&"" ""&TRIM(RIGHT(RC[1],8)),8)))"

    Columns("A:A").Insert Shift:=xlToRight
    With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
        .Range("A1:a2").FormulaR1C1 = X & Y
        .Value = .Value
        .NumberFormat = "dd/mm/yyyy hh:mm AM/PM"
    End With
    Columns("B:B").Delete
End Sub

 

[kungfauxn00b]

Awesome, thank you Mark! This is exactly what I was looking for. Thanks for your help!!