Close.
It still is not giving me minutes.
Also it only gives me hours over 40, not under
See if this solution is okay.
<title>Excel Jeanie HTML</title>
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 76px;"> <col style="width: 56px;"> <col style="width: 72px;"> <col style="width: 62px;"> <col style="width: 65px;"> <col style="width: 75px;"> <col style="width: 109px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="font-weight: bold; text-align: center;">Date</td> <td style="font-weight: bold; text-align: center;">Day</td> <td style="font-weight: bold;">Time In</td> <td style="font-weight: bold;">Lunch Out</td> <td style="font-weight: bold;">Lunch In</td> <td style="font-weight: bold;">Time Out</td> <td style="font-weight: bold;">Total Time</td> <td style="font-weight: bold;">Additional Time</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">2/2/2009</td> <td>Monday</td> <td style="text-align: right;">8:00 AM</td> <td> </td> <td> </td> <td style="text-align: right;">5:40 PM</td> <td style="text-align: right;">9:40</td> <td style="text-align: right;">1:40</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">2/3/2009</td> <td>Tuesday</td> <td style="text-align: right;">8:05 AM</td> <td style="text-align: right;">12:30 PM</td> <td style="text-align: right;">1:15 PM</td> <td style="text-align: right;">5:00 PM</td> <td style="text-align: right;">8:10</td> <td style="text-align: right;">0:10</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">2/4/2009</td> <td>Wednesday</td> <td style="text-align: right;">8:00 AM</td> <td style="text-align: right;">12:00 PM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">6:00 PM</td> <td style="text-align: right;">9:00</td> <td style="text-align: right;">1:00</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">2/5/2009</td> <td>Thursday</td> <td style="text-align: right;">9:00 AM</td> <td> </td> <td> </td> <td style="text-align: right;">6:00 PM</td> <td style="text-align: right;">9:00</td> <td style="text-align: right;">1:00</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">2/6/2009</td> <td>Friday</td> <td style="text-align: right;">7:30 AM</td> <td style="text-align: right;">11:30 AM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">4:00 PM</td> <td style="text-align: right;">7:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td style="font-weight: bold;">Total</td> <td style="text-align: right;">42:50</td> <td style="text-align: right;">2:50</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="text-align: right;">2/2/2009</td> <td>Monday</td> <td style="text-align: right;">8:00 AM</td> <td> </td> <td> </td> <td style="text-align: right;">3:40 PM</td> <td style="text-align: right;">7:40</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="text-align: right;">2/3/2009</td> <td>Tuesday</td> <td style="text-align: right;">8:00 AM</td> <td style="text-align: right;">12:30 PM</td> <td style="text-align: right;">1:15 PM</td> <td style="text-align: right;">2:45 PM</td> <td style="text-align: right;">6:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="text-align: right;">2/4/2009</td> <td>Wednesday</td> <td style="text-align: right;">8:00 AM</td> <td style="text-align: right;">12:00 PM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">4:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="text-align: right;">2/5/2009</td> <td>Thursday</td> <td style="text-align: right;">9:00 AM</td> <td> </td> <td> </td> <td style="text-align: right;">4:00 PM</td> <td style="text-align: right;">7:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="text-align: right;">2/6/2009</td> <td>Friday</td> <td style="text-align: right;">7:30 AM</td> <td style="text-align: right;">11:30 AM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">4:00 PM</td> <td style="text-align: right;">7:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td style="font-weight: bold;">Total</td> <td style="text-align: right;">31:40</td> <td style="color: rgb(255, 0, 0); text-align: right;">-8:20</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>
Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>G8</td> <td>{=INT(
(SUM(MINUTE(G2:G6))/60)+SUM
(HOUR(G2:G6)))&":"&ROUND(
((SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6))-INT((SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6))))*60,0)}</td></tr> <tr> <td>H8</td> <td>=IF(AND
(LEFT(G8,FIND(":",G8)-1)-40>=0,RIGHT(G8,LEN(G8)-FIND(":",G8))>0),LEFT
(G8,FIND(":",G8)-1)-40&":"&RIGHT
(G8,LEN(G8)-FIND(":",G8)),LEFT
(G8,FIND(":",G8)-1)-40+1&":"&60-RIGHT
(G8,LEN(G8)-FIND(":",G8)))</td></tr></tbody></table></td></tr> <tr> <td>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
I have shown two sets here. To convert the additional hour font to red when the total hours worked is less than 40, like in the example set 2 shown above, select H17, goto format - conditional format - type formula: =LEFT(H17,1)="-"
select format and select font color red.
Hope this helped.