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
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

Interesting Management