Variable time format

[Three Lefts]

I have a bunch of data that came from stopwatch readings. Most of it
is under 10 minutes, but occasionally I will have a value as large as
several hours.

Is there a way in Excel, without writing a macro, to display time data
as a number using the largest units (hours, minutes, seconds) having a
value >= 1?

Some examples:

Reading Display
00:00:12.34 12.34 sec
00:00:59.99 59.99 sec
00:01:00.00 1.00 min
00:01:30.00 1.50 min
00:59:30.00 59.50 min
00:59:59.50 59.99 min
00:59:59.69 59.99 min
00:59:59.70 1.00 hrs /* this one is tricky */
01:00:00.00 1.00 hrs
12:34:56.78 12.58 hrs

and so on...

Assume the reading never exceeds 23:23:59.99.

I have written a macro in another language to accomplish this, so I
could port it over to VBA, but if there is a way using custom formats,
I would prefer that.

 

[Pete_UK]

Here's a way using a formula:

=IF(E1>=1/24,TEXT(E1*24,"0.00")&"
hrs",IF(E1>=1/24/60-0.0000001,TEXT(E1*24*60-0.0000001,"0.00")&"
min",TEXT(E1,".00")&" sec"))

copied down - it assumes your times are in column E, starting with E1,
so adjust this if necessary. Values are converted to text in the
helper column.

Hope this helps.

Pete