Paul,
That is exactly what I was looking for, I've been able to modify what
you sent to do what I need and it works great, this will save a ton
of manual inputting. Thanks so much for your help.
On Apr 8, 8:49 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> the problem you have here is that Excel doesn't recognize what you call "time"
> as actual "time".
>
> Excel says that "time" is actually a fractional part of a day.
> since there are 24 hours in a day, 6:00am is 1/4 of a day, (or .25)
> 12:00 noon is 1/2 of a day,
> and 6:00pm is 3/4 of a day.
>
> If you were to change your sample cells to "general" format, you'll find that
> 7:58:31 becomes 0.332303240740741
> but
> :00:00 and :07:36 are unchanged, because Excel doesn't recognize them as valid
> "time" formats.
> So they remain text strings.
>
> Functions like Hour(), Minute() and Second() expect to receive a "time" as a
> parameter.
> Not a text string.
>
> So the first thing we need to do is decide if we're wanting to convert the
> text
> strings to date/time format,
> or convert the date/time to text strings.
>
> To convert the "time" to a text string, you must first determine if the value
> IS
> a time value.
> I put this macro together.
> '=======================================================================
> Option Explicit
> Sub Parse_Time()
> Dim StrArray, LHour, LMinute, LSecond
> Dim TimeString, dtime As Date
> On Error Resume Next
> Err.Clear
> '-----------------------
> ' Save the cell value
> '-----------------------
> TimeString = Range("D2").Value
> '-----------------------------------
> ' save the string in a date variable
> '------------------------------------
> dtime = TimeString
> '--------------------------------------------
> 'If saving it as a date produces an error,
> 'then it evidently wasn't a date!
> ' In that case, convert the date to a text string
> '--------------------------------------------
> If (Err = 0) Then
> TimeString = Format(dtime, "hh:mm:ss")
> End If
> '----------------------------------------------
> ' Split the string into an array
> '----------------------------------------------
> StrArray = Split(TimeString, ":")
> '----------------------------------------------
> ' Store the individual array elements
> '----------------------------------------------
> LHour = StrArray(0)
> If LHour = "" Then LHour = "0"
> LMinute = StrArray(1)
> If LMinute = "" Then LMinute = "0"
> LSecond = StrArray(2)
> If LSecond = "" Then LSecond = "0"
> '----------------------------------------
> ' Display the values
> '----------------------------------------
> Range("L2").Value = LHour
> Range("M2").Value = LMinute
> Range("N2").Value = LSecond
> End Sub
> '==================================================================
>
> Now, if you have a whole bunch of these rows/columns to deal with, we could
> use
> put together a loop like:
> Option Explicit
> Sub Parse_Time()
> Dim R, C, C2, FirstCol, OffsVal
> Dim StrArray, LHour, LMinute, LSecond
> Dim TimeString, dtime As Date
> On Error Resume Next
> FirstCol = 4
> OffsVal = 12
> For R = 2 To 30
> For C = 4 To 6
> Err.Clear
> C2 = (C - FirstCol) * 3 + OffsVal
> '-----------------------
> ' Save the cell value
> '-----------------------
> TimeString = Cells(R, C).Value
> '-----------------------------------
> ' save the string in a date variable
> '------------------------------------
> dtime = TimeString
> '--------------------------------------------
> 'If saving it as a date produces an error,
> 'then it evidently wasn't a date!
> ' In that case, convert the date to a text string
> '--------------------------------------------
> If (Err = 0) Then
> TimeString = Format(dtime, "hh:mm:ss")
> End If
> '----------------------------------------------
> ' Split the string into an array
> '----------------------------------------------
> StrArray = Split(TimeString, ":")
> '----------------------------------------------
> ' Store the individual array elements
> '----------------------------------------------
> LHour = StrArray(0)
> If LHour = "" Then LHour = "0"
> LMinute = StrArray(1)
> If LMinute = "" Then LMinute = "0"
> LSecond = StrArray(2)
> If LSecond = "" Then LSecond = "0"
> '----------------------------------------
> ' Display the values
> '----------------------------------------
> Cells(R, C2).Value = LHour
> Cells(R, C2 + 1).Value = LMinute
> Cells(R, C2 + 2).Value = LSecond
> Next C
> Next R
> End Sub
>
> hope this helps,
>
> Paul S.
>
> ________________________________
> From: Speilman_54 <mbed...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Thu, April 7, 2011 1:49:12 PM
> Subject: $$Excel-Macros$$ Re: parsing time field
>
> Please forgive me I'm not the best programmer. The cells are
> initially a custom field but if you change them to text it isn't
> making a difference because of the : starting value
> Is it possible that you could break down what this is doing
> Range("L2").offset(0,1).resize(1,3).value =
> split(Range(cellalpha).value,":")
> This does work but not entirely of what I'm trying to do
>
> Here is more of an example of what' I'm trying to do
> data comes is as
> D E F
> 7:58:31 :00:00 :07:36
>
> And what I want is
> L M N O P Q R S T
> 7 58 31 0 0 0 0 7 36
>
> as soon as it tries any cell beginning with : I get a type mismatch on
> the first line LHour = Hour(Range(cellalpha)), Maybe I'm not going
> about this the right way.
>
> On Apr 7, 12:35 pm, GoldenLance <samde...@gmail.com> wrote:
>
>
>
> > Assuming the time is a text, and not a time value, use
>
> > Range("L2").offset(0,1).resize(1,3).value =
> > split(Range(cellalpha).value,":")
>
> > On Apr 7, 5:11 pm, Speilman_54 <mbed...@gmail.com> wrote:
>
> > > I'm having an issue where I'm trying to separate a cell with time in
> > > it, for example the cell would conta 8:56:45 and I need each one of
> > > those numbers into it's on cell so A1 = 8 A2 =56 A3=45. So far I've
> > > been able to ge this to work however the system sends the information
> > > will send data like :45:45 if there work time is less than 1 hour
> > > which is where I'm receiving Type Mismatch error. If I could somehow
> > > insert a 0 when this would solve this issue, but I've tried to but
> > > unsuccessfully. Any help would be much appreciated. Code is below
>
> > > 'staffed time
> > > ' hour separator
> > > LHour = Hour(Range(cellalpha))
> > > Range(L2) = LHour
> > > ' minute separator
> > > LMinute = Minute(Range(cellalpha))
> > > Range("M2") = LMinute
> > > ' Seconds separator
> > > LSecond = Second(Range(cellalpha))
> > > Range("N2") = LSecond
>
> --
> ----------------------------------------------------------------------------------
>
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links
> :http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
