Converting time from text to numeric

[Eric Wixom]

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?

 

[David Biddulph]

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

 

[Eric Wixom]

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?

 

[Fred Smith]

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]

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