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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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: )
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912

ADVERTISEMENT

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
 

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279
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
 

sailepaty

Active Member
Joined
Nov 1, 2005
Messages
279

ADVERTISEMENT

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
 

BubbaBBQ

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

BubbaBBQ

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

BubbaBBQ

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

Forum statistics

Threads
1,141,681
Messages
5,707,796
Members
421,529
Latest member
Balintn

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
Top