excel sum time into hours (with base in seconds)

0
votes

My sheet holds a column with all cells having values in seconds (these can be very big numbers). What I want to do is to display (format) them summed up into minutes and hours. The "[hh]:mm:ss" format seems good, but it fails, because my cell contains seconds and excel expects it to be days.

Example. My column looks like this (average time in seconds):

30378.92
14142.78
13837.00
13787.44

after formatting it into time/[hh]:mm:ss I get:

729094:04:48 = 30378.92 * 24 ~= 729094.08
339426:43:12
332088:00:00
330898:33:36

What I'd like to get is:

8:26:18 (30378 seconds = 8 hours + 26 mins + 18.9 secs)

Is it possible to force excel to treat '30378' as number of seconds instead of number of days? I know I can write '00:00:30378' into a cell - is this the only way?

1
exceltimeformatting
Why not just change the format to a number? - RonaldBarzell
If you exceed 24 hours then the [hh]:mm:ss format won't be appropriate anymore. Why not display it in three separate columns that reference your number of seconds? And then if you really really want it to look like hh:mm:ss you could hide those columns and just do a =A1&":"&B1&":"&C1 in a fourth column. - Dan

1 Answers

2
votes

This is quite simple with a helper column. With seconds in A2 just divide that by 86400 (number of seconds in a day) in B2, i.e. this formula in B2

=A2/86400

format B2 as [hh]:mm:ss and you get the correct time value (even if that's over 24 hours - the square brackets ensure that).

You can "fill down" B2 formula for all column A data

If you want to convert the column "in situ" then you can divide the whole column by 86400 without an additional column like this:

Put 86400 in any blank cell then copy that cell > Select column of seconds data > Paste Special > under "operation" select "divide" > OK > ESC > format as [hh]:mm:ss