How to change US date format to UK date format - dd/mm/yyyy

[vbanewbie68]

Hi

Please could you show me how to change from US date to United Kingdom Date like this dd/mm/yyyy?

Thank you in advance

Best regards

V

Sub Add_Rows_Data()
LastRow = Imprt.Cells(1, 1).End(xlDown).Row
DatePaymentMade = WorksheetFunction.Match("DatePaymentMade", Imprt.Cells(1, 1).EntireRow, 0)
For x = 2 To LastRow

Imprt.Cells(x, DatePaymentMade).Value = DateValue(Cells(x, DatePaymentMade).Text) 'Date format

Next x

End Sub

 

[Fluff]

You can use Text to columns for that.
Select column B, On the Data tab select text to columns, delimited, next, clear all checkboxes, next, select MDY from the date drop down, Finish.

 

[vbanewbie68]

You can use Text to columns for that.
Select column B, On the Data tab select text to columns, delimited, next, clear all checkboxes, next, select MDY from the date drop down, Finish.

How to write it on VBA please?
R

 

[Fluff]

Just record a macro whilst you do it manually.

 

[RoryA]

Since VBA defaults to US formats, you could probably just use:

Imprt.Cells(x, DatePaymentMade).Value = Cells(x, DatePaymentMade).value

 

[Dave Patton]

Sub FixDates5()
 ' Converts Text Dates USA format to International or Regional settings on computer
  ' edit the formatting to your preference
  ' USA is MDY  If time is in source data it remains but does not show in the formatting.
  With Selection
    .TextToColumns DataType:=xlDelimited, Tab:=0, Semicolon:=0, Comma:=0, Space:=0, Other:=0, FieldInfo:=Array(1, 5)
    .NumberFormat = "dd-mmm-yyyy"
  End With
End Sub

 

[vbanewbie68]

Since VBA defaults to US formats, you could probably just use:

Imprt.Cells(x, DatePaymentMade).Value = Cells(x, DatePaymentMade).value

Thanks for this and got the date correct but can't rid of the time stamp.

 

[vbanewbie68]

Sub FixDates5()
 ' Converts Text Dates USA format to International or Regional settings on computer
  ' edit the formatting to your preference
  ' USA is MDY  If time is in source data it remains but does not show in the formatting.
  With Selection
    .TextToColumns DataType:=xlDelimited, Tab:=0, Semicolon:=0, Comma:=0, Space:=0, Other:=0, FieldInfo:=Array(1, 5)
    .NumberFormat = "dd-mmm-yyyy"
  End With
End Sub

Hi Dave

Thanks for this which is greatly appreciated. The question is that how to convert your VBA codes into my codes looking at Column B, DatePaymentMade? I am new to VBA code.

Warm regards

V

 

[Dave Patton]

"Thanks for this and got the date correct but can't rid of the time stamp."

Time does not show on your post.
Do you require an edit to the code to ignore the time?

 

[vbanewbie68]

"Thanks for this and got the date correct but can't rid of the time stamp."

Time does not show on your post.
Do you require an edit to the code to ignore the time?

Yes please. Please see below my screen. I use this line Imprt.Cells(x, DatePaymentMade).Value = Cells(x, DatePaymentMade).value.