VBA to change US date format to UK date format

[Apple08]

Hi everyone

I have a report which have dates in American format YYYY-MM-DD, I need to change the date columns from D to M into UK format DD-MM-YYYY and the end row is various. Some fields may be only text instead of dates.

Please could anyone help how to create this in VBA? Many thanks.

 

[Scott T]

See if this works for you.

Sub formatdate()
Dim lr As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row [COLOR=#008000]'change "D" to column that will always have value in it[/COLOR]
Range("D2:M" & lr).NumberFormat = "DD-MM-YYYY"

End Sub

 

[Apple08]

Sorry, I afraid it doesn't work. Dates aren't changed.

 

[Scott T]

Are they excel dates are text?

you can use isnumber to test if it is a number or not, dates in excel are numbers formatted to look like a date. Where D2 has the date

=ISNUMBER(D2)

 

[Apple08]

You are right, they are not numbers as the result is FALSE. So how to change text from 2018-09-17 to 17-09-2018 please?

 

[Scott T]

See if this works for you

Sub formatdate()
Dim lr As Long
lr = Sheets("Sheet6").Cells(Rows.Count, "D").End(xlUp).Row 'change "D" to column that will always have value in it
Sheets("Sheet6").Range("D2:M" & lr).NumberFormat = "DD-MM-YYYY"
For Each cell In Sheets("Sheet6").Range("D2:M" & lr)
    Range(cell.Address) = DateValue(cell)
Next cell
End Sub

 

[Apple08]

I have updated the code to "Sheet1" however, there is run time error 9 out of range. Therefore I changed it to the name of the worksheet "Data". It seems worked. However, there is run time error 13 for

Range(cell.Address) = DateValue(cell)

Is it possible to use the generic Sheet1 instead of the name of the worksheet "Data"?

 

[Scott T]

The code should point to the sheet you are working on so if the sheet is named data then it should point to data. If there are any blank cells in the range you can not get the datevalue of that cell see if this fixes the problem.

Sub formatdate2()
Dim lr As Long
lr = Sheets("Data").Cells(Rows.Count, "D").End(xlUp).Row 'change "D" to column that will always have value in it
Sheets("Data").Range("D2:M" & lr).NumberFormat = "DD-MM-YYYY"


For Each cell In Sheets("Data").Range("D2:M" & lr)
    If cell <> "" Then Range(cell.Address) = DateValue(cell)
Next cell


End Sub

 

[Peter_SSs]

Working on a column (rather than cell) at a time, this should work on the active sheet and assumes no blank columns from D:M.
Test in a copy of your workbook.

Sub Fix_Dates()
  Dim c As Long
  
  For c = 4 To 13   '<- Cols D:M
    Columns(c).TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 5)
    Columns(c).NumberFormat = "dd-mm-yyyy"
  Next c
End Sub

 

[MARK858]

Please ignore as won't make a difference with OP's format