Converting time from text to numeric
 

I have a column of time logs ranging from:

1:00:00:00
to
0:01

I need to split this into days, hours, minutes, and seconds. They are not
the same length so LEFT or RIGHT functions wont work, and I am not savvy
enough in VB to program the split.

Any suggestions?

Converting time from text to numeric
 

If they are *text* as your subject line says, then use Data/ Text to
Columns/ Delimited, and specify the colon as delimiter.
--
David Biddulph

"Eric Wixom" wrote in message
...
I have a column of time logs ranging from:

1:00:00:00
to
0:01

I need to split this into days, hours, minutes, and seconds. They are not
the same length so LEFT or RIGHT functions wont work, and I am not savvy
enough in VB to program the split.

Any suggestions?

Converting time from text to numeric
 

I didn't explain it will, sorry. the values are coming from a counter, it
starts at 0:01 and then when it hits 23:59:59 it will roll over to 1:00:00:00

Excel is not recognixing it as time, so I can't use TIMEVALUE or similar
functions to break it down. Excel is recognizing it as text. I need to be
able to get Excel to recognize the Day, Hour, Minute, and Second. I know in
programing you would use a split function, does Excel have something similar?

"David Biddulph" wrote:

If they are *text* as your subject line says, then use Data/ Text to
Columns/ Delimited, and specify the colon as delimiter.
--
David Biddulph

"Eric Wixom" wrote in message
...
I have a column of time logs ranging from:

1:00:00:00
to
0:01

I need to split this into days, hours, minutes, and seconds. They are not
the same length so LEFT or RIGHT functions wont work, and I am not savvy
enough in VB to program the split.

Any suggestions?

Converting time from text to numeric
 

You can do this in two steps. One, force your data to be consistently
d:hh:mm:ss, using:

=RIGHT("0:00:00:"&IF(OR(LEN(A1)=4,LEN(A1)=7),"0"," ")&A1,10)

Second, convert to days plus time:

=LEFT(A2,1)+TIMEVALUE(RIGHT(A2,8))

This will work for up to 10 days. You can display it using a format like: d
hh:mm:ss

Is this good enough?

Regards,
Fred.

"Eric Wixom" wrote in message
...
I didn't explain it will, sorry. the values are coming from a counter, it
starts at 0:01 and then when it hits 23:59:59 it will roll over to
1:00:00:00

Excel is not recognixing it as time, so I can't use TIMEVALUE or similar
functions to break it down. Excel is recognizing it as text. I need to be
able to get Excel to recognize the Day, Hour, Minute, and Second. I know
in
programing you would use a split function, does Excel have something
similar?

"David Biddulph" wrote:

If they are *text* as your subject line says, then use Data/ Text to
Columns/ Delimited, and specify the colon as delimiter.
--
David Biddulph

"Eric Wixom" wrote in message
...
I have a column of time logs ranging from:

1:00:00:00
to
0:01

I need to split this into days, hours, minutes, and seconds. They are
not
the same length so LEFT or RIGHT functions wont work, and I am not
savvy
enough in VB to program the split.

Any suggestions?

Converting time from text to numeric
 

Fantastic! Thank you!
I had to add LEN=5 and LEN=8 to the formula to make it come out right for
all the different times, but it works great.

The TIMEVALUE is not working correctly though, I don't know why yet. I will
play with it but you won half the battle for me.

Thanks again,
Eric

"Fred Smith" wrote:

You can do this in two steps. One, force your data to be consistently
d:hh:mm:ss, using:

=RIGHT("0:00:00:"&IF(OR(LEN(A1)=4,LEN(A1)=7),"0"," ")&A1,10)

Second, convert to days plus time:

=LEFT(A2,1)+TIMEVALUE(RIGHT(A2,8))

This will work for up to 10 days. You can display it using a format like: d
hh:mm:ss

Is this good enough?

Regards,
Fred.

"Eric Wixom" wrote in message
...
I didn't explain it will, sorry. the values are coming from a counter, it
starts at 0:01 and then when it hits 23:59:59 it will roll over to
1:00:00:00

Excel is not recognixing it as time, so I can't use TIMEVALUE or similar
functions to break it down. Excel is recognizing it as text. I need to be
able to get Excel to recognize the Day, Hour, Minute, and Second. I know
in
programing you would use a split function, does Excel have something
similar?

"David Biddulph" wrote:

If they are *text* as your subject line says, then use Data/ Text to
Columns/ Delimited, and specify the colon as delimiter.
--
David Biddulph

"Eric Wixom" wrote in message
...
I have a column of time logs ranging from:

1:00:00:00
to
0:01

I need to split this into days, hours, minutes, and seconds. They are
not
the same length so LEFT or RIGHT functions wont work, and I am not
savvy
enough in VB to program the split.

Any suggestions?