# Thread: Calculating minutes formula in EXCEL

1. Registered User
Join Date
May 2006
Posts
9

## 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. Registered User
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. Registered User
Join Date
Dec 2003
Location
Dallas, TX
Posts
1,004
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. Registered User
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. Registered User
Join Date
Dec 2003
Location
Dallas, TX
Posts
1,004
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
•