Trim date from date/time

[BubbaBBQ]

I have columns of cells that list date and time. Each cell looks like this:

3/17/2006 3:30:00 PM

I need VBA to loop through and remove the time and leave just the date. When finished it will look like this:

3/17/2006

A simple left trim doesn't work because of varied lengths of month, date, and time. The For Next loop isn't the problem, but I can't figure out what to do to trim the time.

I have tried:

    vCdate = Cells(sRow, 5).Value
    vTrimDate = Left(vCdate, Find(" ", vCdate) - 1)
    Cells(sRow, 5).Value = vTrimDate

This doesn't work. I don't think "Find" is a valid VBA term.

Any suggestions will be appreciated.

Bubba

 

[BubbaBBQ]

Hie to you all,

Supposing your date are in column("E") if not change E to your column letter.

then run this code:

Sub removetime()
For Each ran In Columns("e").Cells
If VarType(ran) = vbDate Then
ran.Value = Int(ran)
ElseIf VarType(ran) = vbString Then
ran.Value = Int(DateValue(ran))
End If
Next
Set ran = Nothing
End Sub

This seems to give me the most hope. The results are converting the time to 0:00. Hopefully, this will give us consistant results in the pivot tables. We'll give a try.

Thanks for the suggest.

Bubba