Format the date using a macro in an excel report

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.

macro excel macro excel macro excel macro excel


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.

Sub test()
' select the date column
' 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 = " hh:mm:ss"
Dim c As Range
' select a range but you can’t select the full column
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)
Exit For
End If
End If
End Sub

Interesting Management