Strip/remove time from date using VBA

[coffeeman12]

I have been using int(cell) to convert dates with time to untimed dates, but this is messy in that I need to fill two additional columns with data. First to do the conversion, than another to paste the absolute cell values rather than a formula.

I'd like to do this in a single step in the same column (happens to be column "C") using VBA. I've tried a few methods people have posted, but none that seem to work for my simple situation.

 

[Peter_SSs]

Try this with a copy of your data. Adjust the date format in the code to suit your date format.

Sub Remove_Time()
    With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .TextToColumns DataType:=xlDelimited, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9))
      .NumberFormat = "d/m/yyyy"
    End With
End Sub

 

[Trebor76]

Hi there,

Change the 'strMyCol' variable (if needed) and then run this while on the sheet in question:

Option Explicit
Sub Macro1()

    Dim strMyCol As String
    Dim lngLastRow As Long
    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    strMyCol = "C" 'Column with dates. Change to suit.
    lngLastRow = Cells(Rows.Count, strMyCol).End(xlUp).Row
    
    For Each rngMyCell In Range(strMyCol & "1:" & strMyCol & lngLastRow)
        'If there's a date in the current cell of 'strMyCol' column, then...
        If IsDate(rngMyCell) = True Then
            '...format it as 'dd/mm/yyyy' in the column to its immediate right. Change to suit.
             rngMyCell.Offset(0, 1).Value = Format(rngMyCell, "dd/mm/yyyy")
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert