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 the macro ?
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.
' select the date column
' asking to display the date in this format, change . by / if your date is dd/mm/yyyy in your PC
Selection.NumberFormat = "dd.mm.yyyy hh:mm"
Dim i As Range
' select a range but you can select the full column
For Each i In Selection
Dim j As String
j = i.Value
i.Value = DateValue(i.Value) + TimeValue(i.Value)