Formula for Counting No. of Hours Worked Based on Se Schedule Excluding Time Outside of Schedule

[Zedrick13]

Hi, everyone. Sorry if the title is kinda vague, but what I was trying to do is coming up with a formula for computing the number of work hours for each employee based on their schedule against their timestamps. Then, also compute their lates and absences. Here's how my tables would look like:

Table 1

Book1
	A	B	C	D
1	Timestamps
2	Staff ID	Date	Time In	Time Out
3	100001	01-Nov	08:59:36	17:01:04
4	100002	01-Nov	08:45:01	17:15:50
5	100003	01-Nov	09:13:05	17:45:01
6	100004	01-Nov	08:07:56	17:30:36
7	100001	02-Nov	08:59:45	17:07:18
8	100002	02-Nov	08:30:31	17:10:22
9	100003	02-Nov	08:56:45	18:03:45
10	100004	02-Nov	08:39:09	17:15:16
11	100001	05-Nov	09:45:33	17:14:10
12	100002	05-Nov	09:20:09	17:04:54
13	100003	05-Nov	09:03:07	16:57:50
14	100002	06-Nov	08:36:47	16:58:01
15	100003	06-Nov	08:54:49	17:11:22
16	100004	06-Nov	08:58:59	17:22:23
Sheet1

Table 2

Book1
	A	B	C	D
18	Schedule
19	Staff ID	Date	Shift Start	Shift End
20	100001	01-Nov	08:00:00	17:00:00
21	100002	01-Nov	08:00:00	17:00:00
22	100003	01-Nov	08:00:00	17:00:00
23	100004	01-Nov	08:00:00	17:00:00
24	100001	02-Nov	08:00:00	17:00:00
25	100002	02-Nov	08:00:00	17:00:00
26	100003	02-Nov	08:00:00	17:00:00
27	100004	02-Nov	08:00:00	17:00:00
28	100001	05-Nov	08:00:00	17:00:00
29	100002	05-Nov	08:00:00	17:00:00
30	100003	05-Nov	08:00:00	17:00:00
31	100004	05-Nov	08:00:00	17:00:00
32	100001	06-Nov	On Leave	On Leave
33	100002	06-Nov	08:00:00	17:00:00
34	100003	06-Nov	08:00:00	17:00:00
35	100004	06-Nov	08:00:00	17:00:00
Sheet1

So, based on the schedule and the timestamps above, I want columns C-F populated with the number of hours worked per each employee per day counted only within the shift start and end based on each employee's timestamp.

Examples:
1. For 10001 (01-Nov) - count only the hours from 08:00:00 to 17:00:00
(Employee timed in early and timed in after shift end - count only the hours between 8AM-5PM) - C39
2. For 10002 (05-Nov) - count only the hours from 09:20:09 to 17:00:00
(Employee timed in late and timed out after 5PM - count only the hours between 09:20:09 to 17:00:00 (5PM)) - E40
3. For 10003 (05-Nov) - count only the hours from 09:03:07 to 16:57:50
(Employee timed in late and timed out before the shift end - count only the hours between 09:03:07 tp 16:57:50) - E41


Table 3

Book1
	A	B	C	D	E	F	G	H
37	Calculation
38	Staff ID	Rate/Hr	01-Nov	02-Nov	05-Nov	06-Nov	Lates	Absences
39	100001	20
40	100002	15
41	100003	15
42	100004	20
Sheet1

 

[etaf]

are the shifts for everyone always 08:00 to 17:00
what other entries can be seen on the schedule sheet ?
you are just showing 08:00 to 17:00 and an entry for "On Leave"
is that all you ever have in the schedule?
Are these tables - just on seperate Sheets on the same excel workbook?

 

[Zedrick13]

The tables and the values included are just for the sake of illustration. The schedule in the spreadsheet I am trying to build will be more varied. The (1) monthly schedule, (2) timestamps, and (3) calculation of hours and salary is going to be on 3 different sheets. Thanks

 

[etaf]

would be worth know the detail - as it may change the solution completely and as we have to lookup and check a few values - it maybe via a reference lookup or a simple nested IF
I maybe disappearing now offline for a while - something has comeup

 

[etaf]

ok, i'm back - but looking at the requirment , is going to need quite a bit of work and so we really need to have all the possible entries in Actual and Scheduled
I suggested a Helper sheet to setup the times to use
Shift patterns , what are all the possibilities
Employee , maximum number you plan to monitor
Days how many days - Just a week ?
At the moment your example shows a Thursday to Tuesday - so do they work over the weekend?
Please answer as much as possible - as i say this could be a lot of work

https://www.dropbox.com/s/1paj6jkulehjtlx/lookup - SCH v ACT.xlsx?dl=0

 

[Zedrick13]

Sorry for the late response. I'll be sure to add the details. I'll just finish it up. Thanks.