Excel Formula - Work Hours Computation

[Zedrick13]

Hi,

Good day!

I hope you guys can help me with coming up with a formula for work hours computation. Here's the link for the spreadsheet:

https://drive.google.com/open?id=1rZDYvB-KvGQFrFMuVjSQdYMDS-TxfzKm

Thanks in advance!

 

[oldbrewer]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Staff No.	Issued Time	Issued Date	issdatetime	Checkout Time	Checkout Date	checkdatetime	Hours Worked
56731	7:24:11	12/12/2018	12 Dec 18 07:24	17:41:46	12/12/2018	12 Dec 18 17:41	10.29305556
58640	7:12:36	12/12/2018	12 Dec 18 07:12	19:30:46	12/12/2018	12 Dec 18 19:30	12.30277778
56756	7:12:24	12/12/2018	12 Dec 18 07:12	19:47:20	12/12/2018	12 Dec 18 19:47	12.58222222
57541	7:11:26	12/12/2018	12 Dec 18 07:11	19:31:23	12/12/2018	12 Dec 18 19:31	12.3325
56735	7:08:32	12/12/2018	12 Dec 18 07:08	19:31:10	12/12/2018	12 Dec 18 19:31	12.37722222
56752	7:08:25	12/12/2018	12 Dec 18 07:08	19:47:26	12/12/2018	12 Dec 18 19:47	12.65027778
56756	7:07:57	12/12/2018	12 Dec 18 07:07	19:46:37	12/12/2018	12 Dec 18 19:46	12.64444444
58673	7:03:25	12/12/2018	12 Dec 18 07:03	19:30:57	12/12/2018	12 Dec 18 19:30	12.45888889
55443	7:00:57	12/12/2018	12 Dec 18 07:00	19:46:42	12/12/2018	12 Dec 18 19:46	12.7625
58793	6:53:37	12/12/2018	12 Dec 18 06:53	19:30:32	12/12/2018	12 Dec 18 19:30	12.61527778
56626	6:46:19	12/12/2018	12 Dec 18 06:46	19:46:49	12/12/2018	12 Dec 18 19:46	13.00833333
57661	19:01:14	12/11/2018	11 Dec 18 19:01	7:51:15	12/12/2018	12 Dec 18 07:51	12.83361111
56755	18:58:58	12/11/2018	11 Dec 18 18:58	7:31:04	12/12/2018	12 Dec 18 07:31	12.535
57178	18:48:25	12/11/2018	11 Dec 18 18:48	7:30:39	12/12/2018	12 Dec 18 07:30	12.70388889
56760	18:46:13	12/11/2018	11 Dec 18 18:46	7:51:14	12/12/2018	12 Dec 18 07:51	13.08361111
55728	18:33:17	12/11/2018	11 Dec 18 18:33	7:30:55	12/12/2018	12 Dec 18 07:30	12.96055556
56735	7:17:54	12/11/2018	11 Dec 18 07:17	19:31:22	12/11/2018	11 Dec 18 19:31	12.22444444
56731	7:17:16	12/11/2018	11 Dec 18 07:17	17:44:50	12/11/2018	11 Dec 18 17:44	10.45944444

<colgroup><col style="width: 61px"><col width="102"><col width="102"><col width="102"><col width="102"><col width="102"><col width="112"><col width="112"></colgroup><tbody>
</tbody>

 

[Zedrick13]

Hi oldbrewer,

Thanks for helping me again. I think your computation isn't based on the shift schedule on the second sheet? Ideally, I would only want to count the number of hours within the shift schedule.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Staff No.	Issued Time	Issued Date	issdatetime	Checkout Time	Checkout Date	checkdatetime	Hours Worked
56731	7:24:11	12/12/2018	12 Dec 18 07:24	17:41:46	12/12/2018	12 Dec 18 17:41	10.29305556
58640	7:12:36	12/12/2018	12 Dec 18 07:12	19:30:46	12/12/2018	12 Dec 18 19:30	12.30277778
56756	7:12:24	12/12/2018	12 Dec 18 07:12	19:47:20	12/12/2018	12 Dec 18 19:47	12.58222222
57541	7:11:26	12/12/2018	12 Dec 18 07:11	19:31:23	12/12/2018	12 Dec 18 19:31	12.3325
56735	7:08:32	12/12/2018	12 Dec 18 07:08	19:31:10	12/12/2018	12 Dec 18 19:31	12.37722222
56752	7:08:25	12/12/2018	12 Dec 18 07:08	19:47:26	12/12/2018	12 Dec 18 19:47	12.65027778
56756	7:07:57	12/12/2018	12 Dec 18 07:07	19:46:37	12/12/2018	12 Dec 18 19:46	12.64444444
58673	7:03:25	12/12/2018	12 Dec 18 07:03	19:30:57	12/12/2018	12 Dec 18 19:30	12.45888889
55443	7:00:57	12/12/2018	12 Dec 18 07:00	19:46:42	12/12/2018	12 Dec 18 19:46	12.7625
58793	6:53:37	12/12/2018	12 Dec 18 06:53	19:30:32	12/12/2018	12 Dec 18 19:30	12.61527778
56626	6:46:19	12/12/2018	12 Dec 18 06:46	19:46:49	12/12/2018	12 Dec 18 19:46	13.00833333
57661	19:01:14	12/11/2018	11 Dec 18 19:01	7:51:15	12/12/2018	12 Dec 18 07:51	12.83361111
56755	18:58:58	12/11/2018	11 Dec 18 18:58	7:31:04	12/12/2018	12 Dec 18 07:31	12.535
57178	18:48:25	12/11/2018	11 Dec 18 18:48	7:30:39	12/12/2018	12 Dec 18 07:30	12.70388889
56760	18:46:13	12/11/2018	11 Dec 18 18:46	7:51:14	12/12/2018	12 Dec 18 07:51	13.08361111
55728	18:33:17	12/11/2018	11 Dec 18 18:33	7:30:55	12/12/2018	12 Dec 18 07:30	12.96055556
56735	7:17:54	12/11/2018	11 Dec 18 07:17	19:31:22	12/11/2018	11 Dec 18 19:31	12.22444444
56731	7:17:16	12/11/2018	11 Dec 18 07:17	17:44:50	12/11/2018	11 Dec 18 17:44	10.45944444

<tbody>
</tbody>

 

[oldbrewer]

i didn't see it lol but 56731 was booked for 7am to 7pm yet clocked in late and clocked out early - and what if somebody starts 7 pm friday until 7 am saturday - does the sat sun allowance take precedence