+ Reply to Thread
Results 1 to 7 of 7

Counting minutes and getting a result in days hoyrs and minutes

  1. #1
    Registered User
    Join Date
    10-07-2012
    Location
    Midlands, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Counting minutes and getting a result in days hoyrs and minutes

    Hi All

    New member, first post, some excel knowledge but just above basic

    I am a scuba diver and have been recording my dive times over the past 10 years all in minutes. Am now transferring some 250 data sets (including the dive time in minutes) to a spreadsheet and want to know a cumulative total for the dive time. This currently stands at 5 days, 8 hours and 54 minutes underwater since I started diving. I can't get exel to tell me that though.

    All help gratefully accepted

    Nick

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Counting minutes and getting a result in days hoyrs and minutes

    if you have numbers as, for example 500 (like 500 minutes) then use this to convert to hours, minutes, seconds:

    =TEXT(A1/60/24,"[h]:mm:ss")

    That's for a single cell.
    You can convert single cell and then SUM them, or you can use :

    =TEXT(SUM(A1:A100)/60/24,"[h]:mm:ss")

    for whole range.
    Last edited by zbor; 10-07-2012 at 05:03 AM.

  3. #3
    Registered User
    Join Date
    10-07-2012
    Location
    Midlands, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Counting minutes and getting a result in days hoyrs and minutes

    Thanks zbor

    Thats great - can I get the formula to add in days as well somehow?

    Cheers

    Nick

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Counting minutes and getting a result in days hoyrs and minutes

    Maybe this:

    =TEXT(SUM(A1:A100)/60/24,"MM/DD hh:mm:ss")

    If you have less than 31 day you can remove MM/

  5. #5
    Registered User
    Join Date
    10-07-2012
    Location
    Midlands, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Counting minutes and getting a result in days hoyrs and minutes

    Hey zbor

    That's brilliant - thanks so much

    All best

    Nick

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Counting minutes and getting a result in days hoyrs and minutes

    This si another version with only days:

    =INT(SUM($A$1:$A$100)/1440)&"d "&TEXT(SUM($A$1:$A$100)/60,"hh:mm:ss")
    Last edited by zbor; 10-11-2012 at 08:57 AM.

  7. #7
    Registered User
    Join Date
    10-07-2012
    Location
    Midlands, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Counting minutes and getting a result in days hoyrs and minutes

    zbor

    That's bordering on genius

    N:o)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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