Calculate Total Hours Utilizing MIN and SEC Cells 1

[BetterBeef]

I am very close to solving this, but for some reason I just can't get the final detail.

I have 6 cells that designated as shown below:

L14 M14 N14 O14
MIN DAY 1 SEC DAY 1 MIN DAY 2 SEC DAY 2

P14 Q14 R14
MIN DAY 3 SEC DAY 3 TOTAL HRS

I also have S14 and T14 which show Total MIN and Total Sec respectively. These cells' formulas are correct but I cannot get TOTAL HRS to work.

Here is my formula for R14, TOTAL HRS:
=INT((INT(SUM(L14,N14,P14)/60))+(INT(SUM(M14,O14,Q14)/60))/60)

This formula works except when the minutes add up to 59 minutes and the seconds add up to equal a minute. When this happens the additional minute gets added to the TOTAL MINS cell (S14). This sets the Total MIN to "00" since the total minutes equals 60. The problem is the Total Hours (R14). When this happens with the formula I am currently using, it does not count the 60 minutes as 1 hour. Instead I get a "0" in the cell R14.

Here is the data that I am currently using to get my miscalculation:

MINS SECS TIME:
L14 = 24 M14 = 54 00:24:54
N14 = 19 O14 = 16 00:19:16
P14 = 16 Q14 = 30 00:16:30

When you add all of these up, you should get 1 HR, 0 MINS, 40 SECS. Instead I get 0 HR, 0 MINS, 40 SECS.

If anyone needs any further clarification, feel free to ask as I don't know if I did the best job clarifying what I am trying to do. I can also send the file if this makes no sense.

Thanks,
Chris

 

[Gavona]

The results you show in the Time column can be obtained in Excel using:
=TIME(0,L14,M14) (for Day 1)
I put these results in U14, V14 and W14)
The total Time may then be found by adding the three individual times:
=W14+V14+U14 I put this in X14

To separate out the Hours, minutes and Seconds
R14: =HOUR(X14)
S14: =Minute(X14)
T14: =Second(X14)

You may want to Format,Cells,Number to format the time to display as you prefer.

There are several Frequently asked questions on this forum about dates and times.

http://www.tek-tips.com/faq.cfm?pid=68

Gavin

 

[Gavona]

If you want to do it the hard way then you must add all the times first then convert to whole hours =INT((SUM(L14,N14,P14)*60+SUM(M14,O14,Q14))/60/60)

Then Add the times as before, deduct the whole hours from the previous calculation and convert to minutes.......



Gavin

 

[BetterBeef]

Thank you for your perfect response! I very much appreciate your help. This was driving me nuts!

Thanks again!