time conversion from a varying data dump

[Guest]

I hope this is an easy one...

I have data that spits out times in a text format that usually appear as
:mm:ss (zero hours are omitted), but in the rare chance it goes over 60min,
it appears as h:mm:ss. How do I account for the varying digit(s) for the
hours when using
TIME(hh,mm,ss)? The time starting with the ":" throws a monkeywrench into my
formulas...

I got it to work for anything under an hour using:
TIME(0,RIGHT(LEFT(A1,3),2),RIGHT(A1,2)), but when its greater than 60min, it
doesn't work (obviously since theres a zero in the hour field).

 

[Guest]

You can format the cell using [] around whatever you don't want to roll over
to the next time unit. Example [mm]:ss will cause there never to be hours
shown. It will show 45:45, 59:45, 68:45, 90:45, 100:45, 2013:45 all
representing minutes and seconds and not hours. You are still stuck with the
number of digits however. Although, you could use the find function in your
formula to look for the ":" and adjust you left,right,mid functions based on
that find....

Hope that is a start.

 

[Guest]

Thats good to know (I wondered what the brackets were for). All I'm really
trying to do is to have a separate column to look at column (data dump) and
convert the text displayed as ":15:37" to equate to a cell formated to time
to display "0:15:37". Additionally if the text comes in as "3:48:12" I want
the same formula to recognize the difference betwen the lack of a zero in the
first example and the addition of a digit in the hours field for the second.
My goal is to use this function:

=TIME( equation for hour, equation for min, equation for sec)

with the use of left mid and right. Unfortunately I'm still grasping the
functionality of left, right and mid, and my coffee hasn't kicked in so I
was hoping someone would just tell me the equation to put in there.

You can format the cell using [] around whatever you don't want to roll over
to the next time unit. Example [mm]:ss will cause there never to be hours
shown. It will show 45:45, 59:45, 68:45, 90:45, 100:45, 2013:45 all
representing minutes and seconds and not hours. You are still stuck with the
number of digits however. Although, you could use the find function in your
formula to look for the ":" and adjust you left,right,mid functions based on
that find....

Hope that is a start.

I hope this is an easy one...

I have data that spits out times in a text format that usually appear as
:mm:ss (zero hours are omitted), but in the rare chance it goes over 60min,
it appears as h:mm:ss. How do I account for the varying digit(s) for the
hours when using
TIME(hh,mm,ss)? The time starting with the ":" throws a monkeywrench into my
formulas...

I got it to work for anything under an hour using:
TIME(0,RIGHT(LEFT(A1,3),2),RIGHT(A1,2)), but when its greater than 60min, it
doesn't work (obviously since theres a zero in the hour field).

 

[David Biddulph]

If your input times always have two digits for minutes and for seconds (e.g.
:05:07), you could try:
=IF(ISNUMBER(A1),A1,TIME(IF(LEFT(A1)=":",0,LEFT(A1,FIND(":",A1)-1)),MID(A1,FIND(":",A1)+1,2),RIGHT(A1,2)))

If they may be formatted as :5:7 then you'll need some extra complication.
--
David Biddulph

Thats good to know (I wondered what the brackets were for). All I'm really
trying to do is to have a separate column to look at column (data dump)
and
convert the text displayed as ":15:37" to equate to a cell formated to
time
to display "0:15:37". Additionally if the text comes in as "3:48:12" I
want
the same formula to recognize the difference betwen the lack of a zero in
the
first example and the addition of a digit in the hours field for the
second.
My goal is to use this function:

=TIME( equation for hour, equation for min, equation for sec)

with the use of left mid and right. Unfortunately I'm still grasping the
functionality of left, right and mid, and my coffee hasn't kicked in so
I
was hoping someone would just tell me the equation to put in there.

You can format the cell using [] around whatever you don't want to roll
over
to the next time unit. Example [mm]:ss will cause there never to be
hours
shown. It will show 45:45, 59:45, 68:45, 90:45, 100:45, 2013:45 all
representing minutes and seconds and not hours. You are still stuck with
the
number of digits however. Although, you could use the find function in
your
formula to look for the ":" and adjust you left,right,mid functions based
on
that find....

Hope that is a start.

I hope this is an easy one...

I have data that spits out times in a text format that usually appear
as
:mm:ss (zero hours are omitted), but in the rare chance it goes over
60min,
it appears as h:mm:ss. How do I account for the varying digit(s) for
the
hours when using
TIME(hh,mm,ss)? The time starting with the ":" throws a monkeywrench
into my
formulas...

I got it to work for anything under an hour using:
TIME(0,RIGHT(LEFT(A1,3),2),RIGHT(A1,2)), but when its greater than
60min, it
doesn't work (obviously since theres a zero in the hour field).