MrExcel Publishing
Your One Stop for Excel Tips & Solutions

To take hh:mm:ss format and change it to seconds for averaging...then back

Posted by Chris Rock on August 15, 2001 7:42 AM

I have a 31 day list with 2 columns: CALLS and DURATION.

When a given day has a CALL, it also has a DURATION. However, with 0 CALLS, there is a DURATION of 0.

The DURATION is in hh:mm:ss format - it gets input from another system. On another part of the sheet, I'd like to take that hh:mm:ss number, break it down into total seconds, and then, ignoring the 0 DURATIONS, I'd like to get the average of all the DURATIONS in seconds, then put that back into a hh:mm:ss format. The biggest problem I have is that the hh:mm:ss value shows as the time-date serial number. This is useless to me when I try to run calculations on it.

Any way to do this? I can use Data / Text to columns to break the hh:mm:ss format into hh | mm | ss - but I am having problem with the averaging, due to the whole 60 seconds in a minute thing.

I'm open to anything, formulas, macros, even chanting magic words.


I can provide a more specific example if needed.

Posted by Mark W. on August 15, 2001 7:56 AM

Chris, perhaps I don't quite understand what you're
trying to accomplish, but... consider this.

If cells A1:A4 contain the following times:


Then, the array formula...


...will compute the average (excluding 0's) of
those times in seconds (13,976 seconds).

[Here's where I get confused ] You request that
these seconds be converted back to hh:mm:ss format!
To do this you'd need to convert the seconds back
into an internal time value using =13976/86400.
I not quite sure where that gets you because
that's the same value you'd get using the array
formula, {=AVERAGE(IF(A1:A4,A1:A4))}, in the 1st

Posted by Chris Rock on August 15, 2001 9:03 AM

I can see how you're confused. I like the Array formula, and with your example, it looks like this will work.

My biggest confusion is Excel's conversion from time format to serial number. Perhaps I don't need to worry about it at all, like your example seems to show.

I will apply the example you've provided to my data and see if it works. Thanks for your help.

The TRICKIEST part of this whole matter is that I am working toward a solution that I already have...I am trying to re-create a solution that has already been calculated. It's hard to explain, but it would make my job alot easier if my spreadsheet could calculate this average.