This is a macro version to format a date in DMY, MDY, YMD, MYD, DYM or YDM. You can do the same thing without macro by using the “text to columns” option, in this case, read my topic Use the time including the date and hour in an excel report.

When I use the macro ?

Every time I extract a date data and after copying/pasting them, excel doesn’t recognize as a date so I need to format it in DMY (day, month, year).

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is/are the macro(s) ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub test()
' select the date column
Columns("B:B").Select
' asking to display the date in this format, change . by / if your date is dd/mm/yyyy in your PC
' If you don’t want the time, just remove hh:mm:ss and the timevalue
Selection.NumberFormat = "dd.mm.yyyy hh:mm:ss"
Dim c As Range
' select a range but you can’t select the full column
Range("B2:B10").Select
For Each c In Selection
  Dim v As String
  v = c.Value
  If c.Value <> Empty Then
    If Trim(c.Value) <> "" Then
      c.Value = DateValue(c.Value) + TimeValue(c.Value)
    Else
      Exit For
    End If
  End If
Next
End Sub