how to remove Hours and minute from text date. ..

[Mallesh23]

Hi Team,

I have seen one post where in dates column. hours and minute to remove and format the date.

I have one question if the given date is in text format, 2017-08-10 : 04:30 , what would be the code,
to remove hours and mintues from it. I have similar kind of situation , One of my colleague told use trim function . but I don't know in vba how to do that. plz help.



Sub RemoveTimeValue()
Dim X As Long, SheetNames As Variant, Cols As Variant
SheetNames = Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")
Cols = Array("J", "L", "P", "R")
For X = LBound(SheetNames) To UBound(SheetNames)
Worksheets(SheetNames(X)).Columns(Cols(X)).Replace " *", "", xlPart
Worksheets(SheetNames(X)).Columns(Cols(X)).NumberFormat = "yyyy-mm-dd"
Next
End Sub

 

[Joe4]

Just find the first space and delete everything after that.

In VBA, take a look at the INSTR function in conjunction with the LEFT function.
MS Excel: How to use the INSTR Function (VBA)

 

[alansidman]

Assume your data is in column A, then in column B =left(A1,10)

 

[MrKowz]

Try:

Public Sub RemoveTimeValue()
Dim SheetNames      As Variant, _
    wsname          As Variant
    
Dim Cols            As Variant, _
    xCol            As Variant
    
Dim i               As Long, _
    LR              As Long, _
    strTmp          As String
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
    
SheetNames = Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")
Cols = Array("J", "L", "P", "R")

For Each wsname In SheetNames
    For Each xCol In Cols
        LR = Sheets(wsname).Cells(Rows.Count, xCol).End(xlUp).Row
        For i = 2 To LR
            With Sheets(wsname).Cells(i, xCol)
                strTmp = .Value
                'Strip off the time by only returning the string which appears before the first space
                strTmp = Left(strTmp, InStr(" ", strTmp) - 1)
                'Convert the returned string to a date and store in the cell's value
                .Value = CDate(strTmp)
            End With
        Next i
    Next xCol
Next wsname

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub