Solved

How to sum duration times in (hh:mm:ss) format

Posted on 2014-11-24
3
920 Views
Last Modified: 2014-11-25
Hello again, Experts:

Call-Summary-by-Extension-111-EE-11-24-1I need a formula that will total duration times in hh:mm:ss format.  See sample attached.  Hoping you can help.

Thanks,
Gary - cincinnati
0
Comment
Question by:garyrobbins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40463387
I can't quickly think of a way to sum the column you specify (it is text), but the "duration in seconds" colums can be easily summed and formatted as hh:mm:ss.
This calculates the total duration for your example worksheet as 102 hours, 29 minutes and 46 seconds
1. Sum and convert to seconds to  days (in Excel, 1 day = 1.0)
=SUM(H2:H226)/60/60/24

2. Then choose custom format for the result cell. This format displays values that exceed one day in hh:mm:ss.
[h]:mm:ss

Edit: On second thoughts, you CAN sum the column you asked for by using an array formula:
{=SUM(VALUE(G2:G226))}
and then formatting as described above [h]:mm:ss

On the Mac I'm sitting at now I have to use Command+Shift+Enter to create the array formula (you can't manually type the curly braces). On Windows it is CTRL+SHIFT+ENTER.
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40463655
First format the cells to be summed to the following

[h]:mm:ss

Then you can just use =SUM(H2:H226).

Ensure that the result cell is also formatted as [h]:mm:ss
0
 

Author Closing Comment

by:garyrobbins
ID: 40464581
Thanks, SimonAdept!
Fast and Simple...

I love EE.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question