Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    9

    Question Unanswered: Calculating minutes formula in EXCEL

    I have an excel file in which a column contains the total number of min spoken against each telephone number but when i calculated the total number of min of all min spoken then i am getting the wrong total. For eg. 5.57 + 0.03 should be = 6 min but what i get is 5.60.

    Can you please let me know what is the correct formulat to generate the correct answer.

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    You write that you have minutes data. The example suggests otherwise. Sounds a little mixed up.

    If you have minutes values, how do you interpret 5.57? Surely 5.57 minutes? Then adding 0.03 minutes should be 5.6 minutes.

    The example sounds more like you don't have decimal minutes but minutes & seconds separated by a "." So 5.57 minutes represents 5 minutes and 57 seconds.

    Suggest you have a look in the Excel help info.

    You can input values in h:mm:ss format and add using normal worksheet formula. I don't usually work with times but do recall they don't always seem to work as simply as you'd hope.

    If you are stuck with your current data it might be easiest to add another column which has a conversion to either time or seconds. Add on that column and convert that total to what you want.

    HTH,
    Fazza

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Quote Originally Posted by toplist
    I have an excel file in which a column contains the total number of min spoken against each telephone number but when i calculated the total number of min of all min spoken then i am getting the wrong total. For eg. 5.57 + 0.03 should be = 6 min but what i get is 5.60.

    Can you please let me know what is the correct formulat to generate the correct answer.
    Hi Toplist,

    Did you Format those cells as TIME?
    Next, in the cell that is to calculate (hold the sum), place this:
    =IF(E12-D12+G12-F12<=0,0,E12-D12+G12-F12)

    For weekly next, place this:
    =IF(ISERR(I11+H12),"",I11+H12)

    Hope this helps you out.
    BUD

  4. #4
    Join Date
    Oct 2004
    Posts
    26
    Alternatively you can try this:

    Dim a, b, c, d As Double



    a = 5.57

    b = 3.05



    If Val("0." & Split(a, ".")(1)) + Val("0." & Split(b, ".")(1)) >= 0.6 Then

    c = Val(Split(a, ".")(0)) + 1 + Val("0." & Split(a, ".")(1)) + b - 0.6

    Else

    c = a + b

    End If

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Dniz
    Alternatively you can try this:

    Dim a, b, c, d As Double



    a = 5.57

    b = 3.05



    If Val("0." & Split(a, ".")(1)) + Val("0." & Split(b, ".")(1)) >= 0.6 Then

    c = Val(Split(a, ".")(0)) + 1 + Val("0." & Split(a, ".")(1)) + b - 0.6

    Else

    c = a + b

    End If
    Hi Dniz,

    What I have in mine once formatted a Time will let you type it in and Format it and Calculate it. Not sure if we both are coming up with the same results. Mine is used as a Time Sheet for my wife's work to track their time In, Lunch and Out.
    So many ways to do one thing...and that's a good thing.

    have a nice one,
    BUD

Posting Permissions

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