VBA Code Help

[markster]

Hey folks

I'm after a bit of macro code that will solve a problem. I have a spreadsheet with thousands of transactions. The date and time of the transaction is in Column B (B7 to B65000). The date and time format is as follows 2020.11.24 02:52:51. I'm only interested in the date of the transaction and not the time. I'm after some macro code that will look down column B and change the date format to DD-MMM-YY and remove the time of the transaction. So in the example I've given it will just be changed to 24-NOV-20. Can Anyone help?

Thanks in advance
Mark

 

[Joe4]

On my XL2010 (on a Windows 10 computer), your code worked whether the values were text or real dates formatted to look like the OP posted.

Yeah, it is just that if they were already dates, some of my functions would be overkill and unnecessary (and things could be simplified a little bit).

 

[Rick Rothstein]

Here is another macro that will work with either text or real dates...

Sub MyDateConvertMacro()

  With Range("B7", Cells(Rows.Count, "B").End(xlUp))
    .Replace " *", "", xlPart, , , , False, False
    .Replace ".", "/"
    .NumberFormat = "dd-mmm-yy"
  End With
   
End Sub

 

[markster]

Thanks everyone much appreciated. Will try the solutions now. Cheers