# Sum of Operating Times do Not Appear to Add Correctly

#### dwgnome

##### Active Member
In my spreadsheet I track the hours each piece of equipment operates in hours and minutes with a maximum per row of 24 hours. I have two columns that total the starts and stops. Column AL displays hours and minutes while Column AM displays the equivalent in hourly decimal form. Although the Daily Total displays appear correct, when I Sum all CG equipment hours at AL31 and AM31, I get two different answers where they should be equivalent (same).

Note that 0:00 hours represents 24 hours and zero minutes operating time when there is data entered in Column B of that same row otherwise it is truly 0 hours and 0 minutes.

Perhaps it has to do with how Excel stores the actual number in the cell versus display or my formula computing the hours. For example, when I use evaluate formula on the Daily Total for CG hours at AL11, the number displayed on the formula bar is 23.8264 while the value displayed shows 19:50 or 19 hours and 50 minutes. Oddly, the conversion to decimal in AM11 seems to use the 19:50 to get the 19.83 hours which is correct. The Daily Total in hours at AM31 displays 87.166 hours which is the correct answer.

Can someone assist me in making the sum of the Daily totals in Col AL (by equipment type) add and display correctly? In this example, the value should be 87 hours and 9.99 minutes or 87:09:59 or 87:10 if rounded to nearest minute. Thank you in advance.

Excel Workbook
ABCDEFGHIJKLMNOPQALAM
2Day1234Daily Total (Hrs:Mins)Daily Total (Hrs)
3TimeModeFTReasonTimeModeFTReasonTimeModeFTReasonTimeModeFTReason
41******************
5CG0:00StartG*************0:0024.00
6DB0:00StartG*6:09StopG48:39StartG212:10StopG49:399.65
7AX10:00RunG*4:05StopG*5:05StartG*13:30StopG*12:3012.50
8Ax20:00RunG*5:00StopG*18:45StartG**StopG*10:1510.25
9KX0:04Run**************23:5624.00
102******************
11CG4:10StartG*************19:5019.83
12DB8:30StartG*21:35StopG*********13:0413.07
13AX10:00RunG*4:55StopG*********4:554.92
14Ax2****************0:000.00
15KX****************0:000.00
Start Stop
Excel Workbook
ALAM
3195.6387.17
3273.4534.87
Start Stop

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### shift-del

##### Well-known Member
Hello

Sorry, because you're withholding information I can only guess that the problem is caused in the columns R, V, Z, AD and/or AH.
Or the formula in column AL is wrong. If you change the format in column AL to YYYY-MM-DD hh:mm you will see that it calculates 23 or 24 days (not hours).

#### dwgnome

##### Active Member
To keep the spreadsheet image at a reasonable width, I hid the columns R through AK. However, all of the rows in those columns are blank. The formula is only dependent on the columns with time in them.

The DailyTotals are supposed to be only times not dates. When I changed the times of the start/stops to your suggested format, they all come out as Jan 1, 1900 and the time indicated. Changing the format on the daily total columns seems change to Jan 24, 1900 and the time indicated.

Perhaps the last portion of my formula where I subtract from 24 is actually a day not a time. But the formula seems to provide the correct "time" as I intended.

Note that the answer in AM31 is correct answer I am looking for. I am puzzled as to why the total in AL31 is not it's equivalent as inidcated within the individual cells in the same column.

#### shift-del

##### Well-known Member
Here is my guesswork:
<table valign="middle" colspan="20" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28"><col width="23"><col width="29"><col width="32"><col width="15"><col width="39"><col width="29"><col width="32"><col width="15"><col width="39"><col width="29"><col width="32"><col width="15"><col width="39"><col width="29"><col width="32"><col width="15"><col width="39"><col width="105"><col width="78"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="20" align="middle">Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td><td align="middle">G</td><td align="middle">H</td><td align="middle">I</td><td align="middle">J</td><td align="middle">K</td><td align="middle">L</td><td align="middle">M</td><td align="middle">N</td><td align="middle">O</td><td align="middle">P</td><td align="middle">Q</td><td align="middle">AL</td><td align="middle">AM</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td style="background-color: rgb(255, 255, 204);" align="left">Day</td><td style="background-color: rgb(255, 255, 204);" align="right">1</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">2</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">3</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">4</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="left">Daily Total (Hrs:Mins)</td><td style="background-color: rgb(255, 255, 204);" align="left">Daily Total (Hrs)</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="left">Time</td><td style="background-color: rgb(255, 255, 204);" align="left">Mode</td><td style="background-color: rgb(255, 255, 204);" align="left">FT</td><td style="background-color: rgb(255, 255, 204);" align="left">Reason</td><td style="background-color: rgb(255, 255, 204);" align="left">Time</td><td style="background-color: rgb(255, 255, 204);" align="left">Mode</td><td style="background-color: rgb(255, 255, 204);" align="left">FT</td><td style="background-color: rgb(255, 255, 204);" align="left">Reason</td><td style="background-color: rgb(255, 255, 204);" align="left">Time</td><td style="background-color: rgb(255, 255, 204);" align="left">Mode</td><td style="background-color: rgb(255, 255, 204);" align="left">FT</td><td style="background-color: rgb(255, 255, 204);" align="left">Reason</td><td style="background-color: rgb(255, 255, 204);" align="left">Time</td><td style="background-color: rgb(255, 255, 204);" align="left">Mode</td><td style="background-color: rgb(255, 255, 204);" align="left">FT</td><td style="background-color: rgb(255, 255, 204);" align="left">Reason</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td><td style="background-color: rgb(255, 255, 204);" align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="right">1</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">00:00</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">0,00</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td style="background-color: rgb(204, 255, 204);" align="left">CG</td><td align="right">00:00</td><td align="left">Start</td><td align="left">G</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">00:00</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">0,00</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td style="background-color: rgb(204, 255, 204);" align="left">DB</td><td align="right">00:00</td><td align="left">Start</td><td align="left">G</td><td align="right">
</td><td align="right">06:09</td><td align="left">Stop</td><td align="left">G</td><td align="right">4</td><td align="right">08:39</td><td align="left">Start</td><td align="left">G</td><td align="right">2</td><td align="right">12:10</td><td align="left">Stop</td><td align="left">G</td><td align="right">4</td><td style="font-weight: bold;" align="right">09:40</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">9,67</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">7</td><td style="background-color: rgb(204, 255, 204);" align="left">AX1</td><td align="right">00:00</td><td align="left">Run</td><td align="left">G</td><td align="right">
</td><td align="right">04:05</td><td align="left">Stop</td><td align="left">G</td><td align="right">
</td><td align="right">05:05</td><td align="left">Start</td><td align="left">G</td><td align="right">
</td><td align="right">13:30</td><td align="left">Stop</td><td align="left">G</td><td align="right">
</td><td style="font-weight: bold;" align="right">12:30</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">12,50</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">8</td><td style="background-color: rgb(204, 255, 204);" align="left">Ax2</td><td align="right">00:00</td><td align="left">Run</td><td align="left">G</td><td align="right">
</td><td align="right">05:00</td><td align="left">Stop</td><td align="left">G</td><td align="right">
</td><td align="right">18:45</td><td align="left">Start</td><td align="left">G</td><td align="right">
</td><td align="right">
</td><td align="left">Stop</td><td align="left">G</td><td align="right">
</td><td style="font-weight: bold;" align="right">05:00</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">5,00</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">9</td><td style="background-color: rgb(204, 255, 204);" align="left">KX</td><td align="right">00:04</td><td align="left">Run</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">00:00</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">0,00</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">10</td><td align="right">2</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">00:00</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">0,00</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">11</td><td style="background-color: rgb(204, 255, 204);" align="left">CG</td><td align="right">04:10</td><td align="left">Start</td><td align="left">G</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">00:00</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">0,00</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">12</td><td style="background-color: rgb(204, 255, 204);" align="left">DB</td><td align="right">08:30</td><td align="left">Start</td><td align="left">G</td><td align="right">
</td><td align="right">21:35</td><td align="left">Stop</td><td align="left">G</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">13:05</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">13,08</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">13</td><td style="background-color: rgb(204, 255, 204);" align="left">AX1</td><td align="right">00:00</td><td align="left">Run</td><td align="left">G</td><td align="right">
</td><td align="right">04:55</td><td align="left">Stop</td><td align="left">G</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">04:55</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">4,92</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">14</td><td style="background-color: rgb(204, 255, 204);" align="left">Ax2</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">00:00</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">0,00</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">15</td><td style="background-color: rgb(204, 255, 204);" align="left">KX</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td style="font-weight: bold;" align="right">00:00</td><td style="color: rgb(0, 128, 0); font-weight: bold;" align="right">0,00</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">16</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">17</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">18</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="left">Total</td><td align="right">45:10</td><td align="right">45,17</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">19</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="left">CG</td><td align="right">00:00</td><td align="right">0</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">20</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="left">DB</td><td align="right">22:45</td><td align="right">22,75</td></tr></tbody></table>
<table valign="middle" colspan="3" style="color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(170, 238, 170);"><td>Zelle</td><td>Format</td><td align="right">Wert</td></tr><tr><td>AL18</td><td>[hh]:mm</td><td align="right">1,88194444444444</td></tr><tr><td>AL19</td><td>[hh]:mm</td><td align="right">0</td></tr><tr><td>AL20</td><td>[hh]:mm</td><td align="right">0,947916666666667</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>AL4</td><td>=SUM(IF(AND(B4<>"",F4<>""),F4-B4,0),IF(AND(J4<>"",N4<>""),N4-J4,0))</td></tr><tr><td>AM4</td><td>=AL4*24</td></tr><tr><td>AL18</td><td>=SUM(AL4:AL15)</td></tr><tr><td>AM18</td><td>=SUM(AM4:AM15)</td></tr><tr><td>AL19</td><td>=SUMPRODUCT((\$A\$4:\$A\$15=\$Q19)*(AL\$4:AL\$15))</td></tr><tr><td>AM19</td><td>=SUMPRODUCT((\$A\$4:\$A\$15=\$Q19)*(AM\$4:AM\$15))</td></tr></tbody></table>

#### dwgnome

##### Active Member
Hi shift-del,

Just got back from Holiday to find your proposed solution. Thank you. It worked for the special case as provided. However in my real spreadsheet I have more columns with start and stop times and some just end on “start” times – meaning that the unit is in operation from that time point forward. Also I had to adjust your formula to account for the 00:00 in Column B and no other times to equal 24 hours of operation. In my spreadsheet the data starts on row 5 instead of 4 as shown above.

So using your basic formula I modified to suit as follows:

In AL5 (as Hours and Minutes of operating time):

Code:
``=SUM(IF(AND(B4<>"",F4<>""),F4-B4,IF(AND(B4<>"",F4=""),(F4+1)-B4,0)))+SUM(IF(AND(J4<>"",N4<>""), N4-J4,IF(AND(J4<>"",N4=""),(1-J4),0)))+SUM(IF(AND(R4<>"",V4<>""),V4-R4,IF(AND(R4<>"",V4=""),(1-R4),0)))+SUM(IF(AND(Z4<>"",AD4<>""),AD4-Z4,IF(AND(Z4<>"",AD4=""),(1-Z4),0)))+SUM(IF((AH4=""),0,IF(AH4<>"",(1-AH4),0))``

In AM5 (Hours in decimal form):

=AL4*24

Total for all:

=SUMIF(AL3:AL19,">0",AL3:AL19)

Totals for CTG:

Code:
``=SUMPRODUCT((\$A\$4:\$A\$15=\$AJ22)*(AL\$4:AL\$15))``

Where AJ22 holds the letters CTG

As an alternative I can also use:

Code:
``=SUMPRODUCT(ISNUMBER(SEARCH(AJ26,\$A\$4:\$A\$15))*(AL\$4:AL\$15))``

Where AJ26 is any portion of the letters CTG.

I did some checks and it appears to work now.

One more inquiry: If there is a cleaner/ more efficient formula to represent my formula in AL5 I would appreciate some ideas.

Also it would be nice to have AL5 display as 24:00 instead of zero yet have the underlying value still be a number in [hh]:mm format. Does anyone have any idea on this one?

Thank you for your help.

Replies
2
Views
54
Replies
12
Views
615
Replies
9
Views
110
Replies
1
Views
210
Replies
0
Views
224