Start time | Finish time | Hours worked | Billable hours | |||||||
Tuesday 29 August 18:00 | Wednesday 30 August 07:00 | 13 | 20 | |||||||
Sunday 03 September 18:00 | Monday 04 September 06:00 | 12 | 24 | |||||||
is start day Sunday | before 8pm | after 8am | tot hours | reg hours | premium hours | prem rate | hours | |||
29/08/2017 21:00 | 30/08/2017 05:00 | no | 0.00 | 0.00 | 8.00 | 0.00 | 8.00 | 1.5 | 12 | |
29/08/2017 21:00 | 30/08/2017 06:00 | no | 0.00 | 0.00 | 9.00 | 0.00 | 9.00 | 1.5 | 13.5 | |
29/08/2017 21:00 | 30/08/2017 07:00 | no | 0.00 | 0.00 | 10.00 | 0.00 | 10.00 | 1.5 | 15 | |
29/08/2017 21:00 | 30/08/2017 08:00 | no | 0.00 | 0.00 | 11.00 | 0.00 | 11.00 | 1.5 | 16.5 | |
29/08/2017 21:00 | 30/08/2017 09:00 | no | 0.00 | 1.00 | 12.00 | 1.00 | 11.00 | 1.5 | 17.5 | |
29/08/2017 21:00 | 30/08/2017 10:00 | no | 0.00 | 2.00 | 13.00 | 2.00 | 11.00 | 1.5 | 18.5 | |
29/08/2017 21:00 | 30/08/2017 11:00 | no | 0.00 | 3.00 | 14.00 | 3.00 | 11.00 | 1.5 | 19.5 | |
ignore all above here | ||||||||||
non Sunday working | ||||||||||
finish time | ||||||||||
this cell is B26 | 05:00 | 06:00 | 07:00 | 08:00 | 09:00 | 10:00 | 11:00 | |||
start | 17:00 | 16.5 | 18 | 19.5 | 21 | 22 | 23 | 24 | ||
time | 18:00 | 15.5 | 17 | 18.5 | 20 | 21 | 22 | 23 | ||
19:00 | 14.5 | 16 | 17.5 | 19 | 20 | 21 | 22 | |||
20:00 | 13.5 | 15 | 16.5 | 18 | 19 | 20 | 21 | |||
21:00 | 12 | 13.5 | 15 | 16.5 | 17.5 | 18.5 | 19.5 | |||
non Sunday | ||||||||||
start | end | hours | ||||||||
18:00 | 07:00 | #N/A | ||||||||
#N/A formula is | ||||||||||
=OFFSET($B$26,MATCH(B38,B27:B31,0),MATCH(C38,C26:I26,0)) | ||||||||||
why is this formula returning #N/A please ? | ||||||||||
<colgroup><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>