Sum of Operating Times do Not Appear to Add Correctly

[dwgnome]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	AL	AM
2	Day	1				2				3				4				Daily Total (Hrs:Mins)	Daily Total (Hrs)
3		Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason	Time	Mode	FT	Reason
4	1	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	CG	0:00	Start	G	*	*	*	*	*	*	*	*	*	*	*	*	*	0:00	24.00
6	DB	0:00	Start	G	*	6:09	Stop	G	4	8:39	Start	G	2	12:10	Stop	G	4	9:39	9.65
7	AX1	0:00	Run	G	*	4:05	Stop	G	*	5:05	Start	G	*	13:30	Stop	G	*	12:30	12.50
8	Ax2	0:00	Run	G	*	5:00	Stop	G	*	18:45	Start	G	*	*	Stop	G	*	10:15	10.25
9	KX	0:04	Run	*	*	*	*	*	*	*	*	*	*	*	*	*	*	23:56	24.00
10	2	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
11	CG	4:10	Start	G	*	*	*	*	*	*	*	*	*	*	*	*	*	19:50	19.83
12	DB	8:30	Start	G	*	21:35	Stop	G	*	*	*	*	*	*	*	*	*	13:04	13.07
13	AX1	0:00	Run	G	*	4:55	Stop	G	*	*	*	*	*	*	*	*	*	4:55	4.92
14	Ax2	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	0:00	0.00
15	KX	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	0:00	0.00
Start Stop

Excel Workbook
	AL	AM
31	95.63	87.17
32	73.45	34.87
Start Stop

 

[shift-del]

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]

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]

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]

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):

=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:

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

Where AJ22 holds the letters CTG

As an alternative I can also use:

=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.