Really strange time request

[badinvestor]

I just got a workbook with a Total Time column. However, this column while formatted as h:mm:ss actually represents the time as d:hh:mm. The co worker would like to be able to add JUST the minute column without it converting each 60 min to another hour. e.g.
A2
0:13:50
0:13:50
would represent 0 days, 13 hours and 50 min, twice. But the person wants the minutes (50 *2) to just be added up to equal 100 NOT 1 hour and 40 min. I tried just doing a =right(A2,2) to grab the minutes and then just summing those but it's bringing back the excel serial number not the value shown.

Thanks for any help!

 

[Trouttrap2]

You can convert the time value number to text, then take the RIGHT 2 digits, then coerce the text back to a number by adding a zero. Like this
=RIGHT(TEXT(C1,"h:mm:ss"),2)+0

 

[badinvestor]

I think that will work. "TEXT" should've known! Just over thinking it I guess. Thanks!

 

[Scott Huish]

If you just want decimal minutes, perhaps:

=SUMPRODUCT(SECOND(A1:A100))

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">0:13:50</td><td > </td><td style="text-align:right; ">100</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">0:13:50</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=SUMPRODUCT(SECOND<span style=' color:008000; '>(A1:A100)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>