Trim date from date/time

BubbaBBQ

Board Regular
Joined
Jan 29, 2003
Messages
68
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:

Code:
    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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

Why dont you just do a text to columns and separate by a space? Seems a much simpler way to do this?

My 2 cents (or pennies in my case :biggrin: )
 
Upvote 0
I know that you want VBA code to do the "trimming" for you, but, if I highlight the "date time" on any cell, then do a Format, Number, Date, and select mm/dd/yy, I get the result you want. Why not use your code to format the date-time to show only date?
 
Upvote 0
hi,

the equivalent for FIND-in-a-string in VBA is INSTR
Code:
Option Explicit

Sub test()
Dim rng As Range
Dim arr As Variant
Dim LR As Long
Dim i As Long
Dim j As Integer
Dim temp As String

LR = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A1:A" & LR)
arr = rng

    For i = 1 To rng.Rows.Count
        With Application
        arr(i, 1) = Left(arr(i, 1), InStr(1, arr(i, 1), " "))
        End With
    Next i

rng = arr

End Sub
for more info about this technique using an array see http://puremis.net/excel/code/053.shtml

kind regards,
Erik
 
Upvote 0
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
 
Upvote 0
You should change the format after eliminate de time. So, here is one more option.

Code:
Sub Change()
    Dim Rng As Range
    For Each Rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Rng = Format(Rng, "m/d/yyyy")
        Rng.NumberFormat = "m/d/yyyy"
    Next
End Sub


Regards
 
Upvote 0
One more just in case the original value is text.

Code:
Sub Change()
    Dim Rng As Range
    For Each Rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Rng.NumberFormat = "m/d/yyyy"
        Rng = Format(CDate(Rng), "m/d/yyyy")
    Next
End Sub


Regards
 
Upvote 0
Hi,

Why dont you just do a text to columns and separate by a space? Seems a much simpler way to do this?

My 2 cents (or pennies in my case :biggrin: )

Thanks for the suggestion. We're currently doing this. We're trying to automate the whole process so one macro handles the whole process.

Bubba
 
Upvote 0
I know that you want VBA code to do the "trimming" for you, but, if I highlight the "date time" on any cell, then do a Format, Number, Date, and select mm/dd/yy, I get the result you want. Why not use your code to format the date-time to show only date?

We were originally doing this. Then went to the Text to Columns method because pivot table formulas were effected by the time later in the process. We're trying to automate the whole process and remove the manual steps.

Thanks for the suggestion.

Bubba
 
Upvote 0
hi,

the equivalent for FIND-in-a-string in VBA is INSTR
Code:
Option Explicit

Sub test()
Dim rng As Range
Dim arr As Variant
Dim LR As Long
Dim i As Long
Dim j As Integer
Dim temp As String

LR = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A1:A" & LR)
arr = rng

    For i = 1 To rng.Rows.Count
        With Application
        arr(i, 1) = Left(arr(i, 1), InStr(1, arr(i, 1), " "))
        End With
    Next i

rng = arr

End Sub
for more info about this technique using an array see http://puremis.net/excel/code/053.shtml

kind regards,
Erik

Thanks for the suggestion. I ran this code of the follwing dates:

10/3/2005 0:00
10/3/2005 0:00
10/3/2005 0:00
10/3/2005 0:00
2/16/2006 15:06
3/17/2006 15:30
5/12/2006 15:30
3/2/2007 0:00
5/18/2007 15:30
5/18/2007 15:30
11/5/2007 10:48
5/6/2008 9:33

These are the results:

*
*
*
*
2/16/2006 0:00
3/17/2006 0:00
5/12/2006 0:00
*
5/18/2007 0:00
5/18/2007 0:00
11/5/2007 0:00
5/6/2008 0:00

The * represent blank cells. So if the time = 0:00 then it erased the whole cell. If the time did not = 0:00 then it changed the value to that.

Thanks anyway and I appreciate the tip on the INSTR.

Bubba
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top