+ Reply to Thread
Results 1 to 6 of 6

Adding Durations Help (Without start/end)

  1. #1
    Registered User
    Join Date
    11-27-2013
    Location
    Big Lake, MN
    MS-Off Ver
    Excel 2010
    Posts
    6

    Adding Durations Help (Without start/end)

    Hello,
    Need some rudamentry help adding together durations in 00:00:00 format. Need cell K8 to calculate the duration total in column D. Will be using attached sheet as a master database for data collection efforts to track call durations and totals. Will be adding records daily going forward.

    Would like the formulas to auto calculate as records are added. The current way is not feasible (=D2+D3+D4+D5+D6+D7+D8+D9+D10+D11+D12+D13+D14+D15+D16+D17+D18+D19) and would have to add each cell in manually. Also auto SUM doesnt seem to work in this scenario. Any advice would be greatly appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Adding Durations Help (Without start/end)

    Sum won't work on those values because they are text, if you want to keep it the way it is, try this:
    =SUMPRODUCT(D2:D19*1)
    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Adding Durations Help (Without start/end)

    Your time durations are actually stored as text values, so they don't respond to a SUM. You can change them quite easily - highlight column D by clicking on the D identifier for that column. Then click on Data | Text-to-columns and a dialogue box will pop up - just click Finish and the conversion is done.

    Then in cell K9 you can have this formula:

    =SUM(D:D)

    and it doesn't need to be adjusted if you add new data.

    Hope this helps.

    Pete

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Adding Durations Help (Without start/end)

    Looks like I got to the show a bit late... Pete provided the answer I was going to offer, but I will add to the discussion by pointing out that your formulas in cells K1:K4 can be changed to:

    K1: =COUNT(A:A)
    K2: =COUNTIF(E:E,"Phone")
    K3: =COUNTIF(E:E,"Voicemail")
    K4: =COUNTIF(E:E,"Footprint")

    By doing so you won't have to worry about changing the ranges beyond 100

    - Moo

  5. #5
    Registered User
    Join Date
    11-27-2013
    Location
    Big Lake, MN
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Adding Durations Help (Without start/end)

    Thanks everyone for the answers! Dredwolf's answer seems to do the trick. I get that the formula had an issue with the cell being formatted for text, but I could not get the duration to stick in that format without it changing to a time. The formula provided seems to calculate without having to sacrifice the data's format. Also thanks to Moo the Dog for providing another usable formula that will come in handy.

    A wonderfully pleasant experience

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Adding Durations Help (Without start/end)

    You are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adding Start and End Buttons to Track Breaks
    By sam2424 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2013, 01:04 PM
  2. [SOLVED] Start row with zero and continue adding.
    By visha_1984 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2013, 06:01 AM
  3. Excel 2007 : Adding Time Durations
    By FF-EMT Bourque in forum Excel General
    Replies: 1
    Last Post: 04-13-2012, 01:11 PM
  4. Replies: 8
    Last Post: 03-29-2010, 09:20 PM
  5. [SOLVED] Adding a Message Box to start a Macro
    By kyle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-10-2006, 02:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1