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