+ Reply to Thread
Results 1 to 6 of 6

Adding time in minute/second format together AND an IF formula...

  1. #1
    Registered User
    Join Date
    10-07-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    9

    Post Adding time in minute/second format together AND an IF formula...

    Hi folks,

    Couple of issues here here...

    Everything under the "duration" column needs to be added together and tallied at the bottom...
    Only problem is that I need it to be kept in the same mm:ss format and the format feature is not working well.

    The solid line bar is also creating headaches - because of how the file exports into excel, the columns are all messed up too. I think this is the cause of my headaches.

    I'm also curious is there is an "IF" for sorting...

    For example...
    If column K is the phone number listed in the column, can that entire row be highlighted?

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Adding time in minute/second format together AND an IF formula...

    I'm used to working with Siemens and Avaya telecommunications platforms, so I've run into this sort of issue a few times. Crystal Reports and PDF conversions can do this as well.


    The first thing you should do after exporting is select all of your data by clicking in the upper left corner and choose Merge & Center to cancel all of the merging. Next you'll need to autosize all columns.

    There's a few paths you can take from here to get your numbers to work since the export format is text but not text.

    Option1: =SUM(AD3:AD25*1)

    This must be entered as an array formula by exiting the cell using Ctrl+Shift+Enter after you type in the formula.


    Option2: Type a 1 somewhere. Copy that cell. Then select AD3:AD25, right click, Paste Special -> Multiply -> Hit okay.

    Now =SUM(AD3:AD25) works fine


    As for formatting:

    Option A: Wherever you sum, hit Ctrl+1 to change the format. Go to Custom, change it to [h]:mm to get the full count of hours.

    Option B: You could also append your formulas to =Text(SUM(AD3:AD25*1),"[h]:mm") and =Text(SUM(AD3:AD25*1),"[h]:mm") respectively, but while that would make your outputs pretty without using Custom Format it would be changing everything back to text.
    Last edited by daffodil11; 10-07-2013 at 05:58 PM.

  3. #3
    Registered User
    Join Date
    10-07-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding time in minute/second format together AND an IF formula...

    Hello daffodil11

    Thanks so much for this - never thought to use the array (that's freaking awesome!)

    My data in that column is in minutes:seconds .... It should be about 2 hours 30 minutes or so but I am getting 17 hours and 13 minutes total with the data I have (same as in the spreadsheet provided). I think I am formatting wrong. Is there another feature/custom format I should be using?

    I am wondering if there is a way to make Excel realize that I am giving them 0 hours, x number of minutes and x number of seconds... is there a way to teach excel to read the cell as:
    0:02:55
    0 hours 2 minutes 55 seconds
    Last edited by KFavulous; 10-07-2013 at 06:10 PM.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Adding time in minute/second format together AND an IF formula...

    If you format result cell as [h]:mm with square brackets then the result is actually 161:13 (hh:mm never goes above 23:59)....but that is treating your data as hours and minutes - you can divide by 60 to make that right, try this formula

    =SUMPRODUCT(AD3:AD25/60)

    that gives me 2:41, i.e. 2 hours 41 minutes
    Audere est facere

  5. #5
    Registered User
    Join Date
    10-07-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding time in minute/second format together AND an IF formula...

    Hi daddylonglegs - this is fantastic - thank you so much! that's exactly what I was looking for. Amazing. Thank you both again!

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Adding time in minute/second format together AND an IF formula...

    Hurray! Glad I could help out.

+ 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 elapsed time to date/time format over 24h
    By KimSenger in forum Excel General
    Replies: 1
    Last Post: 04-02-2013, 06:28 AM
  2. RE: Adding Time Format
    By Sloth in forum Excel General
    Replies: 0
    Last Post: 11-02-2005, 06:17 PM
  3. [SOLVED] format:Adding large minute and second values
    By Fred Smith in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 10:05 PM
  4. [SOLVED] 0-60 minute time format
    By Drew Remsen in forum Excel General
    Replies: 1
    Last Post: 07-29-2005, 08:05 AM
  5. [SOLVED] convert time from 60 minute hour to 100 minute hour
    By Jboerding in forum Excel General
    Replies: 2
    Last Post: 07-06-2005, 06:30 PM

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