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

 

[ranman256]

(note never write over original data) so change the statement: ActiveCell.Offset(0, 5).Value =
to write the new date format to. The 5 means it will put the new date in G. (5 cells to the right of B)
change the 5 to whatever empty column you want.

Public Sub CvtDate()
Dim m, d, y, vWord
Dim vDat As Date
Range("B7").Select
While ActiveCell.Value <> ""
   vWord = ActiveCell.Value
   y = Left(vWord, 4)
   m = Mid(vWord, 6, 2)
   d = Mid(vWord, 9, 2)

   vDat = m & "/" & d & "/" & y
   ActiveCell.Offset(0, 5).Value = Format(vDat, "dd-mmm-yyyy")

   ActiveCell.Offset(1, 0).Select 'next row
Wend
End Sub

 

[JLGWhiz]

Se;letct the entire column with the date/time entries and right click. Select 'Format Cellds' from the popup menu. Selefct Number from the Format dialog box. Then selec t'Date' and scroll to the format you want to use. Select that format and click OK. Your date/time ill be donverted to that format and any future entries in that column (other than copied cells) will be converted to that format. If the exqact format is not found in the forgoing process, then select 'Custom' and type in the format you want to use in the narrow box at top right of the dialog box and then clidk OK.

Note: This leaves your original data intact and simply changes the way it is displayed. That provides a means for sorting the dates by value and other advantages over removing the original entries and replacing them with non date data types.

 

[Joe4]

Try this:

Sub MyDateConvertMacro()

    Dim lr As Long
    Dim cell As Range
    Dim dte() As String
       
    Application.ScreenUpdating = False
   
'   Format column B
    Columns("B:B").NumberFormat = "dd-mmm-yy"
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through all rows in column B
    For Each cell In Range("B7:B" & lr)
        dte = Split(cell.Value, " ")
        cell.Value = CDate(Replace(dte(0), ".", "/"))
    Next cell
   
    Application.ScreenUpdating = True
   
    MsgBox "Conversion complete"
   
End Sub

 

[Joe4]

ranman256,

Try to avoidi the use of "ActiveCell" and "Select" lines in your VBA code, especially within loops.
They are totally unnecessary, and it can really slow down your VBA code, especially if you have tens of thousands of rows to go through.

 

[Yongle]

Try this

Sub ConvertValues()
    Dim Cel As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Rng.NumberFormat = "DD-MMM-YYYY"
    For Each Cel In Range("B7:B65000")
        If Cel > 0 And Cel < 50000 Then Cel = Int(Cel.Value)
    Next Cel
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Joe4]

I guess the important question we forgot to ask is if the entry is actually numeric or text (as I noticed Yongle's code seems to assume Numeric and mine assumes Text).

There is an easy way to check. If your first entry is in cell B7, what does this return?
=ISNUMBER(B7)

 

[Rick Rothstein]

Try this:

Sub MyDateConvertMacro()

    Dim lr As Long
    Dim cell As Range
    Dim dte() As String
       
    Application.ScreenUpdating = False
   
'   Format column B
    Columns("B:B").NumberFormat = "dd-mmm-yy"
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Loop through all rows in column B
    For Each cell In Range("B7:B" & lr)
        dte = Split(Range("B7"), " ")
        cell.Value = CDate(Replace(dte(0), ".", "/"))
    Next cell
   
    Application.ScreenUpdating = True
   
    MsgBox "Conversion complete"
   
End Sub

I think what I highlighted in red above should be replaced with cell.Value.

 

[Rick Rothstein]

I guess the important question we forgot to ask is if the entry is actually numeric or text (as I noticed Yongle's code seems to assume Numeric and mine assumes Text).

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.

 

[Joe4]

I think what I highlighted in red above should be replaced with cell.Value.

Great catch, Rick! I started with a simple example, and built the process out, and missed that update.
Thanks