Stuck

N

Nicc79

New Member
Joined
May 5, 2011
Messages
5
With a little help i have been devising a roster see below, i thought that all the formulas worked great but ive now noticed the night allowance for Saturday & Sunday now doesnt work basically if someone works between the hours of 24:00 and 07:00 they get night allowance


Roster

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 86px;"> <col style="width: 86px;"> <col style="width: 49px;"> <col style="width: 50px;"> <col style="width: 85px;"> <col style="width: 85px;"> <col style="width: 50px;"> <col style="width: 50px;"> <col style="width: 60px;"> <col style="width: 60px;"> <col style="width: 60px;"> <col style="width: 78px;"> <col style="width: 62px;"> <col style="width: 73px;"> <col style="width: 74px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>W</td> <td>X</td> <td>Y</td> <td>Z</td> <td>AA</td> <td>AB</td> <td>AC</td> <td>AD</td> <td>AE</td> <td>AF</td> <td>AG</td> <td>AH</td> <td>AI</td> <td>AJ</td> <td>AK</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">IF NIGHTSHIFT FINISHES EARLIER THAN 07:00 HRS ON SUN OR MON AM PLEASE INFORM PAYROLL </td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; font-size: 11pt;" colspan="2">Need to add as date & time</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="text-align: center; font-size: 11pt;" colspan="2">Need to add as date & time</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: center; font-size: 12pt; font-weight: bold;" colspan="2">14/05/2011 23:00</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: center; font-size: 12pt; font-weight: bold;" colspan="2">15/05/2011 23:00</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;" colspan="2">14/05/2011</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;" colspan="2">15/05/2011</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 63px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: center; font-size: 12pt; font-weight: bold;" colspan="2">Saturday</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">Hrs</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">Night A</td> <td style="text-align: center; font-size: 12pt; font-weight: bold;" colspan="2">Sunday</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">Hrs</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">Night A</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">Totals</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">Sun Pre</td> <td style="text-align: center; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">Nights</td> <td style="text-align: center; font-size: 12pt; font-weight: bold;">BH Double T</td> <td style="text-align: center; font-size: 12pt; font-weight: bold;">BH Allow</td> <td style="font-size: 12pt; font-weight: bold;">Hols</td> <td style="font-size: 12pt; font-weight: bold;">Training</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; font-size: 12pt;">08:00:00</td> <td style="text-align: right; font-size: 12pt;">18:00:00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">10.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">1</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">10.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">10.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">1</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; font-size: 12pt;">20:00:00</td> <td style="text-align: right; font-size: 12pt;">07:00:00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">11.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">1</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">11.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">4.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">1</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">23</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 21px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">24</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt;">
</td> <td style="font-size: 12pt;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">26</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="font-size: 12pt; font-weight: bold;">
</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">21.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">2</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">21.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">14.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">2</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td> <td style="text-align: right; background-color: rgb(153, 204, 0); font-size: 12pt; font-weight: bold;">0.00</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"><tbody><tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>W9</td> <td>=DATES!F2</td></tr> <tr> <td>AA9</td> <td>=DATES!G2</td></tr> <tr> <td>Y11</td> <td>=(X11-W11)*24</td></tr> <tr> <td>Z11</td> <td>=IF(AND(TIME(HOUR(X11),MINUTE(X11),SECOND(X11))>=TIME(0,3,0),TIME(HOUR(X11),MINUTE(X11),SECOND(X11))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC11</td> <td>=(AB11-AA11)*24</td></tr> <tr> <td>AD11</td> <td>=IF(AND(TIME(HOUR(AB11),MINUTE(AB11),SECOND(AB11))>=TIME(0,3,0),TIME(HOUR(AB11),MINUTE(AB11),SECOND(AB11))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE11</td> <td>=SUM(AC11,Y11,U11,Q11,M11,I11,E11)</td></tr> <tr> <td>AF11</td> <td>=IF(AND(WEEKDAY(W11)=1,WEEKDAY(X11)=1),Y11,IF(WEEKDAY(W11)=1,(ROUNDUP(W11,0)-W11)*24,IF(WEEKDAY(X11)=1,(X11-ROUNDDOWN(X11,0))*24,0)))+IF(AND(WEEKDAY(AA11)=1,WEEKDAY(AB11)=1),AC11,IF(WEEKDAY(AA11)=1,(ROUNDUP(AA11,0)-AA11)*24,IF(WEEKDAY(AB11)=1,(AB11-ROUNDDOWN(AB11,0))*24,0)))</td></tr> <tr> <td>AG11</td> <td>=F11+J11+N11+R11+V11+Z11+AD11</td></tr> <tr> <td>Y12</td> <td>=(X12-W12)*24</td></tr> <tr> <td>Z12</td> <td>=IF(AND(TIME(HOUR(X12),MINUTE(X12),SECOND(X12))>=TIME(0,3,0),TIME(HOUR(X12),MINUTE(X12),SECOND(X12))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC12</td> <td>=(AB12-AA12)*24</td></tr> <tr> <td>AD12</td> <td>=IF(AND(TIME(HOUR(AB12),MINUTE(AB12),SECOND(AB12))>=TIME(0,3,0),TIME(HOUR(AB12),MINUTE(AB12),SECOND(AB12))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE12</td> <td>=SUM(AC12,Y12,U12,Q12,M12,I12,E12)</td></tr> <tr> <td>AF12</td> <td>=IF(AND(WEEKDAY(W12)=1,WEEKDAY(X12)=1),Y12,IF(WEEKDAY(W12)=1,(ROUNDUP(W12,0)-W12)*24,IF(WEEKDAY(X12)=1,(X12-ROUNDDOWN(X12,0))*24,0)))+IF(AND(WEEKDAY(AA12)=1,WEEKDAY(AB12)=1),AC12,IF(WEEKDAY(AA12)=1,(ROUNDUP(AA12,0)-AA12)*24,IF(WEEKDAY(AB12)=1,(AB12-ROUNDDOWN(AB12,0))*24,0)))</td></tr> <tr> <td>AG12</td> <td>=F12+J12+N12+R12+V12+Z12+AD12</td></tr> <tr> <td>Y13</td> <td>=(X13-W13)*24</td></tr> <tr> <td>Z13</td> <td>=IF(AND(TIME(HOUR(X13),MINUTE(X13),SECOND(X13))>=TIME(0,3,0),TIME(HOUR(X13),MINUTE(X13),SECOND(X13))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC13</td> <td>=(AB13-AA13)*24</td></tr> <tr> <td>AD13</td> <td>=IF(AND(TIME(HOUR(AB13),MINUTE(AB13),SECOND(AB13))>=TIME(0,3,0),TIME(HOUR(AB13),MINUTE(AB13),SECOND(AB13))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE13</td> <td>=SUM(AC13,Y13,U13,Q13,M13,I13,E13)</td></tr> <tr> <td>AF13</td> <td>=IF(AND(WEEKDAY(W13)=1,WEEKDAY(X13)=1),Y13,IF(WEEKDAY(W113)=1,(ROUNDUP(W13,0)-W13)*24,IF(WEEKDAY(X13)=1,(X13-ROUNDDOWN(X13,0))*24,0)))+IF(AND(WEEKDAY(AA13)=1,WEEKDAY(AB13)=1),AC13,IF(WEEKDAY(AA13)=1,(ROUNDUP(AA13,0)-AA13)*24,IF(WEEKDAY(AB13)=1,(AB13-ROUNDDOWN(AB13,0))*24,0)))</td></tr> <tr> <td>AG13</td> <td>=F13+J13+N13+R13+V13+Z13+AD13</td></tr> <tr> <td>Y14</td> <td>=(X14-W14)*24</td></tr> <tr> <td>Z14</td> <td>=IF(AND(TIME(HOUR(X14),MINUTE(X14),SECOND(X14))>=TIME(0,3,0),TIME(HOUR(X14),MINUTE(X14),SECOND(X14))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC14</td> <td>=(AB14-AA14)*24</td></tr> <tr> <td>AD14</td> <td>=IF(AND(TIME(HOUR(AB14),MINUTE(AB14),SECOND(AB14))>=TIME(0,3,0),TIME(HOUR(AB14),MINUTE(AB14),SECOND(AB14))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE14</td> <td>=SUM(AC14,Y14,U14,Q14,M14,I14,E14)</td></tr> <tr> <td>AF14</td> <td>=IF(AND(WEEKDAY(W14)=1,WEEKDAY(X14)=1),Y14,IF(WEEKDAY(W114)=1,(ROUNDUP(W14,0)-W14)*24,IF(WEEKDAY(X14)=1,(X14-ROUNDDOWN(X14,0))*24,0)))+IF(AND(WEEKDAY(AA14)=1,WEEKDAY(AB14)=1),AC14,IF(WEEKDAY(AA14)=1,(ROUNDUP(AA14,0)-AA14)*24,IF(WEEKDAY(AB14)=1,(AB14-ROUNDDOWN(AB14,0))*24,0)))</td></tr> <tr> <td>AG14</td> <td>=F14+J14+N14+R14+V14+Z14+AD14</td></tr> <tr> <td>Y15</td> <td>=(X15-W15)*24</td></tr> <tr> <td>Z15</td> <td>=IF(AND(TIME(HOUR(X15),MINUTE(X15),SECOND(X15))>=TIME(0,3,0),TIME(HOUR(X15),MINUTE(X15),SECOND(X15))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC15</td> <td>=(AB15-AA15)*24</td></tr> <tr> <td>AD15</td> <td>=IF(AND(TIME(HOUR(AB15),MINUTE(AB15),SECOND(AB15))>=TIME(0,3,0),TIME(HOUR(AB15),MINUTE(AB15),SECOND(AB15))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE15</td> <td>=SUM(AC15,Y15,U15,Q15,M15,I15,E15)</td></tr> <tr> <td>AF15</td> <td>=IF(AND(WEEKDAY(W15)=1,WEEKDAY(X15)=1),Y15,IF(WEEKDAY(W115)=1,(ROUNDUP(W15,0)-W15)*24,IF(WEEKDAY(X15)=1,(X15-ROUNDDOWN(X15,0))*24,0)))+IF(AND(WEEKDAY(AA15)=1,WEEKDAY(AB15)=1),AC15,IF(WEEKDAY(AA15)=1,(ROUNDUP(AA15,0)-AA15)*24,IF(WEEKDAY(AB15)=1,(AB15-ROUNDDOWN(AB15,0))*24,0)))</td></tr> <tr> <td>AG15</td> <td>=F15+J15+N15+R15+V15+Z15+AD15</td></tr> <tr> <td>Y16</td> <td>=(X16-W16)*24</td></tr> <tr> <td>Z16</td> <td>=IF(AND(TIME(HOUR(X16),MINUTE(X16),SECOND(X16))>=TIME(0,3,0),TIME(HOUR(X16),MINUTE(X16),SECOND(X16))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC16</td> <td>=(AB16-AA16)*24</td></tr> <tr> <td>AD16</td> <td>=IF(AND(TIME(HOUR(AB16),MINUTE(AB16),SECOND(AB16))>=TIME(0,3,0),TIME(HOUR(AB16),MINUTE(AB16),SECOND(AB16))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE16</td> <td>=SUM(AC16,Y16,U16,Q16,M16,I16,E16)</td></tr> <tr> <td>AF16</td> <td>=IF(AND(WEEKDAY(W16)=1,WEEKDAY(X16)=1),Y16,IF(WEEKDAY(W116)=1,(ROUNDUP(W16,0)-W16)*24,IF(WEEKDAY(X16)=1,(X16-ROUNDDOWN(X16,0))*24,0)))+IF(AND(WEEKDAY(AA16)=1,WEEKDAY(AB16)=1),AC16,IF(WEEKDAY(AA16)=1,(ROUNDUP(AA16,0)-AA16)*24,IF(WEEKDAY(AB16)=1,(AB16-ROUNDDOWN(AB16,0))*24,0)))</td></tr> <tr> <td>AG16</td> <td>=F16+J16+N16+R16+V16+Z16+AD16</td></tr> <tr> <td>Y17</td> <td>=(X17-W17)*24</td></tr> <tr> <td>Z17</td> <td>=IF(AND(TIME(HOUR(X17),MINUTE(X17),SECOND(X17))>=TIME(0,3,0),TIME(HOUR(X17),MINUTE(X17),SECOND(X17))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC17</td> <td>=(AB17-AA17)*24</td></tr> <tr> <td>AD17</td> <td>=IF(AND(TIME(HOUR(AB17),MINUTE(AB17),SECOND(AB17))>=TIME(0,3,0),TIME(HOUR(AB17),MINUTE(AB17),SECOND(AB17))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE17</td> <td>=SUM(AC17,Y17,U17,Q17,M17,I17,E17)</td></tr> <tr> <td>AF17</td> <td>=IF(AND(WEEKDAY(W17)=1,WEEKDAY(X17)=1),Y17,IF(WEEKDAY(W117)=1,(ROUNDUP(W17,0)-W17)*24,IF(WEEKDAY(X17)=1,(X17-ROUNDDOWN(X17,0))*24,0)))+IF(AND(WEEKDAY(AA17)=1,WEEKDAY(AB17)=1),AC17,IF(WEEKDAY(AA17)=1,(ROUNDUP(AA17,0)-AA17)*24,IF(WEEKDAY(AB17)=1,(AB17-ROUNDDOWN(AB17,0))*24,0)))</td></tr> <tr> <td>AG17</td> <td>=F17+J17+N17+R17+V17+Z17+AD17</td></tr> <tr> <td>Y18</td> <td>=(X18-W18)*24</td></tr> <tr> <td>Z18</td> <td>=IF(AND(TIME(HOUR(X18),MINUTE(X18),SECOND(X18))>=TIME(0,3,0),TIME(HOUR(X18),MINUTE(X18),SECOND(X18))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC18</td> <td>=(AB18-AA18)*24</td></tr> <tr> <td>AD18</td> <td>=IF(AND(TIME(HOUR(AB18),MINUTE(AB18),SECOND(AB18))>=TIME(0,3,0),TIME(HOUR(AB18),MINUTE(AB18),SECOND(AB18))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE18</td> <td>=SUM(AC18,Y18,U18,Q18,M18,I18,E18)</td></tr> <tr> <td>AF18</td> <td>=IF(AND(WEEKDAY(W18)=1,WEEKDAY(X18)=1),Y18,IF(WEEKDAY(W118)=1,(ROUNDUP(W18,0)-W18)*24,IF(WEEKDAY(X18)=1,(X18-ROUNDDOWN(X18,0))*24,0)))+IF(AND(WEEKDAY(AA18)=1,WEEKDAY(AB18)=1),AC18,IF(WEEKDAY(AA18)=1,(ROUNDUP(AA18,0)-AA18)*24,IF(WEEKDAY(AB18)=1,(AB18-ROUNDDOWN(AB18,0))*24,0)))</td></tr> <tr> <td>AG18</td> <td>=F18+J18+N18+R18+V18+Z18+AD18</td></tr> <tr> <td>Y19</td> <td>=(X19-W19)*24</td></tr> <tr> <td>Z19</td> <td>=IF(AND(TIME(HOUR(X19),MINUTE(X19),SECOND(X19))>=TIME(0,3,0),TIME(HOUR(X19),MINUTE(X19),SECOND(X19))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC19</td> <td>=(AB19-AA19)*24</td></tr> <tr> <td>AD19</td> <td>=IF(AND(TIME(HOUR(AB19),MINUTE(AB19),SECOND(AB19))>=TIME(0,3,0),TIME(HOUR(AB19),MINUTE(AB19),SECOND(AB19))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE19</td> <td>=SUM(AC19,Y19,U19,Q19,M19,I19,E19)</td></tr> <tr> <td>AF19</td> <td>=IF(AND(WEEKDAY(W19)=1,WEEKDAY(X19)=1),Y19,IF(WEEKDAY(W119)=1,(ROUNDUP(W19,0)-W19)*24,IF(WEEKDAY(X19)=1,(X19-ROUNDDOWN(X19,0))*24,0)))+IF(AND(WEEKDAY(AA19)=1,WEEKDAY(AB19)=1),AC19,IF(WEEKDAY(AA19)=1,(ROUNDUP(AA19,0)-AA19)*24,IF(WEEKDAY(AB19)=1,(AB19-ROUNDDOWN(AB19,0))*24,0)))</td></tr> <tr> <td>AG19</td> <td>=F19+J19+N19+R19+V19+Z19+AD19</td></tr> <tr> <td>Y20</td> <td>=(X20-W20)*24</td></tr> <tr> <td>Z20</td> <td>=IF(AND(TIME(HOUR(X20),MINUTE(X20),SECOND(X20))>=TIME(0,3,0),TIME(HOUR(X20),MINUTE(X20),SECOND(X20))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC20</td> <td>=(AB20-AA20)*24</td></tr> <tr> <td>AD20</td> <td>=IF(AND(TIME(HOUR(AB20),MINUTE(AB20),SECOND(AB20))>=TIME(0,3,0),TIME(HOUR(AB20),MINUTE(AB20),SECOND(AB20))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE20</td> <td>=SUM(AC20,Y20,U20,Q20,M20,I20,E20)</td></tr> <tr> <td>AF20</td> <td>=IF(AND(WEEKDAY(W20)=1,WEEKDAY(X20)=1),Y20,IF(WEEKDAY(W120)=1,(ROUNDUP(W20,0)-W20)*24,IF(WEEKDAY(X20)=1,(X20-ROUNDDOWN(X20,0))*24,0)))+IF(AND(WEEKDAY(AA20)=1,WEEKDAY(AB20)=1),AC20,IF(WEEKDAY(AA20)=1,(ROUNDUP(AA20,0)-AA20)*24,IF(WEEKDAY(AB20)=1,(AB20-ROUNDDOWN(AB20,0))*24,0)))</td></tr> <tr> <td>AG20</td> <td>=F20+J20+N20+R20+V20+Z20+AD20</td></tr> <tr> <td>Y21</td> <td>=(X21-W21)*24</td></tr> <tr> <td>Z21</td> <td>=IF(AND(TIME(HOUR(X21),MINUTE(X21),SECOND(X21))>=TIME(0,3,0),TIME(HOUR(X21),MINUTE(X21),SECOND(X21))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC21</td> <td>=(AB21-AA21)*24</td></tr> <tr> <td>AD21</td> <td>=IF(AND(TIME(HOUR(AB21),MINUTE(AB21),SECOND(AB21))>=TIME(0,3,0),TIME(HOUR(AB21),MINUTE(AB21),SECOND(AB21))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE21</td> <td>=SUM(AC21,Y21,U21,Q21,M21,I21,E21)</td></tr> <tr> <td>AF21</td> <td>=IF(AND(WEEKDAY(W21)=1,WEEKDAY(X21)=1),Y21,IF(WEEKDAY(W121)=1,(ROUNDUP(W21,0)-W21)*24,IF(WEEKDAY(X21)=1,(X21-ROUNDDOWN(X21,0))*24,0)))+IF(AND(WEEKDAY(AA21)=1,WEEKDAY(AB21)=1),AC21,IF(WEEKDAY(AA21)=1,(ROUNDUP(AA21,0)-AA21)*24,IF(WEEKDAY(AB21)=1,(AB21-ROUNDDOWN(AB21,0))*24,0)))</td></tr> <tr> <td>AG21</td> <td>=F21+J21+N21+R21+V21+Z21+AD21</td></tr> <tr> <td>Y22</td> <td>=(X22-W22)*24</td></tr> <tr> <td>Z22</td> <td>=IF(AND(TIME(HOUR(X22),MINUTE(X22),SECOND(X22))>=TIME(0,3,0),TIME(HOUR(X22),MINUTE(X22),SECOND(X22))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC22</td> <td>=(AB22-AA22)*24</td></tr> <tr> <td>AD22</td> <td>=IF(AND(TIME(HOUR(AB22),MINUTE(AB22),SECOND(AB22))>=TIME(0,3,0),TIME(HOUR(AB22),MINUTE(AB22),SECOND(AB22))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE22</td> <td>=SUM(AC22,Y22,U22,Q22,M22,I22,E22)</td></tr> <tr> <td>AF22</td> <td>=IF(AND(WEEKDAY(W22)=1,WEEKDAY(X22)=1),Y22,IF(WEEKDAY(W122)=1,(ROUNDUP(W22,0)-W22)*24,IF(WEEKDAY(X22)=1,(X22-ROUNDDOWN(X22,0))*24,0)))+IF(AND(WEEKDAY(AA22)=1,WEEKDAY(AB22)=1),AC22,IF(WEEKDAY(AA22)=1,(ROUNDUP(AA22,0)-AA22)*24,IF(WEEKDAY(AB22)=1,(AB22-ROUNDDOWN(AB22,0))*24,0)))</td></tr> <tr> <td>AG22</td> <td>=F22+J22+N22+R22+V22+Z22+AD22</td></tr> <tr> <td>Y23</td> <td>=(X23-W23)*24</td></tr> <tr> <td>Z23</td> <td>=IF(AND(TIME(HOUR(X23),MINUTE(X23),SECOND(X23))>=TIME(0,3,0),TIME(HOUR(X23),MINUTE(X23),SECOND(X23))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC23</td> <td>=(AB23-AA23)*24</td></tr> <tr> <td>AD23</td> <td>=IF(AND(TIME(HOUR(AB23),MINUTE(AB23),SECOND(AB23))>=TIME(0,3,0),TIME(HOUR(AB23),MINUTE(AB23),SECOND(AB23))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE23</td> <td>=SUM(AC23,Y23,U23,Q23,M23,I23,E23)</td></tr> <tr> <td>AF23</td> <td>=IF(AND(WEEKDAY(W23)=1,WEEKDAY(X23)=1),Y23,IF(WEEKDAY(W123)=1,(ROUNDUP(W23,0)-W23)*24,IF(WEEKDAY(X23)=1,(X23-ROUNDDOWN(X23,0))*24,0)))+IF(AND(WEEKDAY(AA23)=1,WEEKDAY(AB23)=1),AC23,IF(WEEKDAY(AA23)=1,(ROUNDUP(AA23,0)-AA23)*24,IF(WEEKDAY(AB23)=1,(AB23-ROUNDDOWN(AB23,0))*24,0)))</td></tr> <tr> <td>AG23</td> <td>=F23+J23+N23+R23+V23+Z23+AD23</td></tr> <tr> <td>Y24</td> <td>=(X24-W24)*24</td></tr> <tr> <td>Z24</td> <td>=IF(AND(TIME(HOUR(X24),MINUTE(X24),SECOND(X24))>=TIME(0,3,0),TIME(HOUR(X24),MINUTE(X24),SECOND(X24))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC24</td> <td>=(AB24-AA24)*24</td></tr> <tr> <td>AD24</td> <td>=IF(AND(TIME(HOUR(AB24),MINUTE(AB24),SECOND(AB24))>=TIME(0,3,0),TIME(HOUR(AB24),MINUTE(AB24),SECOND(AB24))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE24</td> <td>=SUM(AC24,Y24,U24,Q24,M24,I24,E24)</td></tr> <tr> <td>AF24</td> <td>=IF(AND(WEEKDAY(W24)=1,WEEKDAY(X24)=1),Y24,IF(WEEKDAY(W124)=1,(ROUNDUP(W24,0)-W24)*24,IF(WEEKDAY(X24)=1,(X24-ROUNDDOWN(X24,0))*24,0)))+IF(AND(WEEKDAY(AA24)=1,WEEKDAY(AB24)=1),AC24,IF(WEEKDAY(AA24)=1,(ROUNDUP(AA24,0)-AA24)*24,IF(WEEKDAY(AB24)=1,(AB24-ROUNDDOWN(AB24,0))*24,0)))</td></tr> <tr> <td>AG24</td> <td>=F24+J24+N24+R24+V24+Z24+AD24</td></tr> <tr> <td>Y25</td> <td>=(X25-W25)*24</td></tr> <tr> <td>Z25</td> <td>=IF(AND(TIME(HOUR(X25),MINUTE(X25),SECOND(X25))>=TIME(0,3,0),TIME(HOUR(X25),MINUTE(X25),SECOND(X25))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AC25</td> <td>=(AB25-AA25)*24</td></tr> <tr> <td>AD25</td> <td>=IF(AND(TIME(HOUR(AB25),MINUTE(AB25),SECOND(AB25))>=TIME(0,3,0),TIME(HOUR(AB25),MINUTE(AB25),SECOND(AB25))>=TIME(7,0,0)),1,0)</td></tr> <tr> <td>AE25</td> <td>=SUM(AC25,Y25,U25,Q25,M25,I25,E25)</td></tr> <tr> <td>AF25</td> <td>=IF(AND(WEEKDAY(W25)=1,WEEKDAY(X25)=1),Y25,IF(WEEKDAY(W125)=1,(ROUNDUP(W25,0)-W25)*24,IF(WEEKDAY(X25)=1,(X25-ROUNDDOWN(X25,0))*24,0)))+IF(AND(WEEKDAY(AA25)=1,WEEKDAY(AB25)=1),AC25,IF(WEEKDAY(AA25)=1,(ROUNDUP(AA25,0)-AA25)*24,IF(WEEKDAY(AB25)=1,(AB25-ROUNDDOWN(AB25,0))*24,0)))</td></tr> <tr> <td>AG25</td> <td>=F25+J25+N25+R25+V25+Z25+AD25</td></tr> <tr> <td>Y26</td> <td>=SUM(Y11:Y25)</td></tr> <tr> <td>Z26</td> <td>=Z11+Z12+Z13+Z14+Z15+Z16+Z17+Z18+Z19+Z20+Z21+Z22+Z23+Z24+Z25</td></tr> <tr> <td>AC26</td> <td>=SUM(AC11:AC25)</td></tr> <tr> <td>AD26</td> <td>=AD11+AD12+AD13+AD14+AD15+AD16+AD17+AD18+AD19+AD20+AD21+AD22+AD23+AD24+AD25</td></tr> <tr> <td>AE26</td> <td>=SUM(AE11:AE25)</td></tr> <tr> <td>AF26</td> <td>=SUM(AF11:AF25)</td></tr> <tr> <td>AG26</td> <td>=SUM(AG11:AG25)</td></tr> <tr> <td>AH26</td> <td>=SUM(AH11:AH25)</td></tr> <tr> <td>AI26</td> <td>=SUM(AI11:AI25)</td></tr> <tr> <td>AJ26</td> <td>=SUM(AJ11:AJ25)</td></tr> <tr> <td>AK26</td> <td>=SUM(AK11:AK25)</td></tr></tbody></table></td></tr></tbody></table>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Here is the formulae I used to get the results you are looking for. I have changed to format in row 12 to, hopefully, make it easier to read.

Copy the formula from "Y11:Z11" to "AC11:AD11"

I can't see what you are trying to do with the column "Sun Pre".

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:93px;" /><col style="width:114px;" /><col style="width:71px;" /><col style="width:70px;" /><col style="width:88px;" /><col style="width:108px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:76px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td><td >AD</td><td >AE</td><td >AF</td><td >AG</td></tr><tr style="height:60px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td colspan="2" style="font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Saturday</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Hrs</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Night A</td><td colspan="2" style="font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Sunday</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Hrs</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Night A</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Total Hrs</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Sun Pre</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:center; ">Total Nights</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Verdana; font-size:12pt; text-align:right; ">08:00:00</td><td style="font-family:Verdana; font-size:12pt; text-align:right; ">18:00:00</td><td style="background-color:#ffff99; font-family:Verdana; font-size:11pt; text-align:right; ">10.00</td><td style="background-color:#ccffff; font-family:Verdana; font-size:11pt; text-align:right; ">0.00</td><td style="font-family:Verdana; font-size:12pt; text-align:right; ">19:00:00</td><td style="font-family:Verdana; font-size:12pt; text-align:right; ">01:00:00</td><td style="background-color:#ffff99; font-family:Verdana; font-size:11pt; text-align:right; ">6.00</td><td style="background-color:#ccffff; font-family:Verdana; font-size:11pt; text-align:right; ">1.00</td><td style="background-color:#ffff99; font-family:Verdana; text-align:right; ">16.00</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:right; ">10</td><td style="background-color:#ccffff; font-family:Verdana; text-align:right; ">1.00</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Verdana; font-size:12pt; text-align:right; ">08:00 PM</td><td style="font-family:Verdana; font-size:12pt; text-align:right; ">08:00 AM</td><td style="background-color:#ffff99; font-family:Verdana; font-size:11pt; text-align:right; ">12.00</td><td style="background-color:#ccffff; font-family:Verdana; font-size:11pt; text-align:right; ">7.00</td><td style="font-family:Verdana; font-size:12pt; text-align:right; ">08:00 PM</td><td style="font-family:Verdana; font-size:12pt; text-align:right; ">04:00 AM</td><td style="background-color:#ffff99; font-family:Verdana; font-size:11pt; text-align:right; ">8.00</td><td style="background-color:#ccffff; font-family:Verdana; font-size:11pt; text-align:right; ">4.00</td><td style="background-color:#ffff99; font-family:Verdana; text-align:right; ">20.00</td><td style="background-color:#99cc00; font-weight:bold; font-family:Verdana; font-size:12pt; text-align:right; ">4</td><td style="background-color:#ccffff; font-family:Verdana; text-align:right; ">11.00</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 >Y11</td><td >=IF(X11>W11,X11-W11,1+X11-W11)*24</td></tr><tr><td >Z11</td><td >=IF(X11>W11,0,IF<span style=' color:008000; '>(X11*24>7,7,X11*24)</span>)</td></tr><tr><td >AE11</td><td >=SUMIF($E$10:$AC$10,"Hrs",E11:AC11)</td></tr><tr><td >AG11</td><td >=SUMIF($E$10:$AD$10,"Night A",E11:AD11)</td></tr></table></td></tr></table> <br /><br />

Edit:
Columns "Y:Z" "AC:AD" are formatted as numbers to two decimal places. As are columns "AE" and "AG".
Column "Y:X" are formatted custom "hh:mm:ss"
 
Last edited:
Upvote 0
I had a look at your previous post to see if I could identify what you were trying to get into column "Sun Pre".

If I understand this correctly you want to isolate the hours worked on a Sunday.

The formula is: AF11=Z11+AC11-AD11
i.e.,
Night hours of Saturday + total hours Sunday - night hours Sunday.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Save as text file in a certain format similar to an xml
2
Replies
16
Views
226
rlv01
R
E
  • Question
Adding a custom message.
Replies
1
Views
417
Micron
M
C
  • Question
Data Entry and Report Generation
Replies
2
Views
133
Carla carla
C
J
  • Solved
excel formula for if time exceeds 30 minutes then yes
Replies
2
Views
77
jquinlavin
J
I
  • Solved
Calculate total hours into days, hours, minutes
Replies
3
Views
380
Issie_52
I

Forum statistics

Threads
1,214,376
Messages
6,119,181
Members
448,871
Latest member
hengshankouniuniu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top