Time Conversion

[Jodi Macy]

When I have someone talking more than 6 minutes, my calculation stops
working. (see NUM reference below)

=TIME(0,0,G1*60)
SEC's TIME
G H
477.59 7:57:35
351.43 5:51:25
306.26 5:06:15
367.17 6:07:10
538.60 8:58:35
357.57 5:57:34
515.69 8:35:41
356.45 5:56:27
338.65 5:38:38
559.13 #NUM!
659.52 #NUM!
379.83 6:19:49
274.99 4:34:59
312.54 5:12:32
294.35 4:54:21

 

[Shane Devenshire]

Hi

477.59*60=28655.4

TIME(0,0,28655) = 28655 seconds

24 hours = 86400 seconds

So the above person is talking for 1/3 of a day.

I don't think this is what you want.

 

[Luke M]

From XL help, you can't have values in the time formula greater than 32767.
Thus. your critical number is around 546.1...
This should let you work around it
=TIME(0,IF(A5>546,A5*60-32760,0),IF(A5>546,546*60))

*note* 32760 is 546*60.

Note that again, if it gets too large and the minutes value gets above
32767, an error will occur.

 

[Gary''s Student]

In H1:

=G1/(60*60*24) and copy down and format as time to display:

477.59 07:57.59
351.43 05:51.43
306.26 05:06.26
367.17 06:07.17
538.6 08:58.60
357.57 05:57.57
515.69 08:35.69
356.45 05:56.45
338.65 05:38.65
559.13 09:19.13
659.52 10:59.52
379.83 06:19.83
274.99 04:34.99
312.54 05:12.54
294.35 04:54.35