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.