VBA: Converting US and UK date formats

kungfauxn00b

New Member
Joined
Jul 25, 2017
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:

tbnxDRr.png


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:

Code:
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:

BIJEe3r.png


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!!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Last edited:
Upvote 0
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! :)
 
Upvote 0
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.

Code:
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
 
Upvote 0
Awesome, thank you Mark! This is exactly what I was looking for. Thanks for your help!! :)
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top