Separation of time (duration) in to separate columns

[twolefthands]

I have tried to follow instructions in office assitant but have
failed.

I have put my phone bill into Excel 2000. The call durations are in
one column formatted as hh:mm:ss. I need to separate into separate
columns containing respectively hh mm ss. Can some kind person give me
further guidance, please?

 

[Guest]

twolefthands,
Check out the time functions HOUR, MINUTE and SECOND. So if your time is in cell A1 then use:

=HOUR(A1)
=MINUTE(A1)
=SECOND(A1)

Good Luck,
Mark Graesser
(e-mail address removed)

----- twolefthands wrote: -----

I have tried to follow instructions in office assitant but have
failed.

I have put my phone bill into Excel 2000. The call durations are in
one column formatted as hh:mm:ss. I need to separate into separate
columns containing respectively hh mm ss. Can some kind person give me
further guidance, please?

 

[Peter Atherton]

You need to convert the hous to a serial number. This is
time as a fraction of a day.

Say the time is in B3 In C3 type = B3 then format C3 as
General. (You could omit this and just format B3 as
general)
In D3 type =HOUR(C3), E3 type =MINUTE(C3) and in F3 type
=SECOND(E3).

Regards
Peter

 

[twolefthands]

No luck I'm afraid. I have tried putting info from cell in adjacent
column and formatting the adjacent col as general and making 3 colls
=Hour(A1) Minute(A1) and Second(A1). In each case the error #VALUE is
returned. This happens no matter if A1 is treated as TEXT, TIME, or
Custom hh:mm:ss. In fact the BT listings show hhhh:mm:ss but excel
doesn't seem willing to accept that in the custom format. I'm stumped
but hopefully someone can come up with something else. Could how I
transferred info from BT site have any bearing? I uses copy and paste
not any variety of paste special.

 

[Guest]

twolefthands
Are the times actually numbers? The may have been brought in as text. Try the following text formulas

=LEFT(A1,2) for hour
=MID(A1,4,2) for minute
=RIGHT(A1,2) for second

Wait a second

I just double checked and the HOUR, MINUTE and SECOND function should work even if the time is a text value. Possibly it is a text value with preceding spaces. If this is the case then try

=HOUR(TRIM(A1)

The TRIM function will strip off the spaces. Since the BT (?) site shows hhhh:mm:ss I would bet that a number like 1:00:00 actually has 3 spaces in front of it

Or, if the times are pulled in as 0001:00:00 then the time function can't handle the four digit hour. If this is the case, then use

=HOUR(RIGHT(A1,8)

The hour will roll back to zero if you have a call over 24 hours long

Good Luck
Mark Graesse
(e-mail address removed)
----- twolefthands wrote: ----

No luck I'm afraid. I have tried putting info from cell in adjacen
column and formatting the adjacent col as general and making 3 coll
=Hour(A1) Minute(A1) and Second(A1). In each case the error #VALUE i
returned. This happens no matter if A1 is treated as TEXT, TIME, o
Custom hh:mm:ss. In fact the BT listings show hhhh:mm:ss but exce
doesn't seem willing to accept that in the custom format. I'm stumpe
but hopefully someone can come up with something else. Could how
transferred info from BT site have any bearing? I uses copy and past
not any variety of paste special

 

[twolefthands]

Thanks Mark. Your first suggestion to use Left, Mid and Right
functions worked with one tweak- MID(A1,6,2) for the minutes. In
meantime found another way to deal with matter. BT has facility to
permit download as csv values. I then had the idea to try and strip
the :s from data and then used maths to get to hh mm ss in separate
columns. Now I can see if non BT offers are worth the candle!