How to calculate working hours per Calendar date

[Vommy]

Hello All,

I would appreciate your help to calculate working hours per calendar date/week/month in 24/7/365 operation. Example: Week starts on Monday at 00.00 and ends on Sunday at 23.59.59. I am struggling to find the solution for past midnight shifts (MOD function does not help).

My data:

HoursPerDate.xlsx
	A	B	C
1	EMPLOYEE	START TIME	END TIME
2	ACK	02/02/2022 22:00	03/02/2022 10:00
3	ACK	04/02/2022 22:00	05/02/2022 06:00
4	ACK	14/02/2022 06:30	14/02/2022 18:30
5	ACK	15/02/2022 06:30	15/02/2022 18:30
6	ACK	16/02/2022 06:30	16/02/2022 16:30
7	ATA	02/02/2022 22:00	03/02/2022 10:00
8	ATA	04/02/2022 22:00	05/02/2022 06:00
9	ATA	14/02/2022 06:30	14/02/2022 18:30
10	ATA	15/02/2022 06:30	15/02/2022 18:30
11	ATA	16/02/2022 06:30	16/02/2022 18:30
12	AUB	02/02/2022 22:00	03/02/2022 10:00
13	AUB	04/02/2022 22:00	05/02/2022 06:00
14	AUB	14/02/2022 06:30	14/02/2022 18:30
15	AUB	15/02/2022 06:30	15/02/2022 18:30
16	AUB	16/02/2022 06:30	16/02/2022 18:30
17	BOZ	02/02/2022 22:00	03/02/2022 10:00
18	BOZ	04/02/2022 22:00	05/02/2022 06:00
19	BOZ	15/02/2022 07:00	15/02/2022 14:00
20	BOZ	16/02/2022 07:00	16/02/2022 15:00
21	BOZ	19/02/2022 06:30	19/02/2022 18:00
22	BRA	02/02/2022 22:00	03/02/2022 10:00
23	BRA	04/02/2022 07:30	04/02/2022 15:30
24	BRA	14/02/2022 09:00	14/02/2022 21:00
25	BRA	15/02/2022 14:30	15/02/2022 23:30
26	BRA	16/02/2022 14:30	16/02/2022 23:30
27	BRK	02/02/2022 22:00	03/02/2022 10:00
28	BRK	04/02/2022 22:00	05/02/2022 06:00
29	BRK	14/02/2022 14:30	14/02/2022 23:30
30	BRK	15/02/2022 14:30	15/02/2022 23:30
31	BRK	16/02/2022 14:30	16/02/2022 23:30
32	BRK	18/02/2022 18:30	19/02/2022 06:30
33	BRK	19/02/2022 18:30	20/02/2022 02:30
34	BUČ	13/02/2022 14:30	13/02/2022 22:30
35	BUČ	14/02/2022 14:30	14/02/2022 23:30
36	BUČ	15/02/2022 14:30	15/02/2022 23:30
37	BUL	02/02/2022 14:00	02/02/2022 22:00
38	BUL	04/02/2022 14:00	04/02/2022 22:00
39	BUL	14/02/2022 06:30	14/02/2022 18:30
40	BUL	15/02/2022 06:30	15/02/2022 18:30
41	BUL	16/02/2022 06:30	16/02/2022 18:30
42	DIZ	02/02/2022 22:00	03/02/2022 10:00
43	DIZ	04/02/2022 22:00	05/02/2022 06:00
44	DIZ	14/02/2022 06:30	14/02/2022 18:30
45	DIZ	15/02/2022 06:30	15/02/2022 18:30
46	DIZ	16/02/2022 06:30	16/02/2022 16:30
47	EDI	02/02/2022 22:00	03/02/2022 10:00
48	EDI	04/02/2022 22:00	05/02/2022 06:00
49	EDI	13/02/2022 02:30	13/02/2022 11:00
50	EDI	14/02/2022 06:30	14/02/2022 18:30
51	EDI	15/02/2022 06:30	15/02/2022 18:30
52	EDI	16/02/2022 06:30	16/02/2022 16:30
53	FAT	02/02/2022 22:00	03/02/2022 10:00
54	FAT	04/02/2022 22:00	05/02/2022 06:00
55	FAT	14/02/2022 06:30	14/02/2022 18:30
56	FAT	15/02/2022 06:30	15/02/2022 18:30
57	FAT	16/02/2022 06:30	16/02/2022 18:30
58	GEO	02/02/2022 22:00	03/02/2022 10:00
59	GEO	04/02/2022 22:00	05/02/2022 06:00
60	GEO	14/02/2022 06:30	14/02/2022 18:30
61	GEO	15/02/2022 06:30	15/02/2022 18:30
62	GEO	16/02/2022 06:30	16/02/2022 18:30
63	GLA	02/02/2022 22:00	03/02/2022 10:00
64	GLA	14/02/2022 06:30	14/02/2022 18:30
65	GLA	15/02/2022 06:30	15/02/2022 18:30
66	GLA	16/02/2022 06:30	16/02/2022 18:30
67	GOL	14/02/2022 09:00	14/02/2022 21:00
68	GOL	15/02/2022 14:30	15/02/2022 23:30
69	GOL	16/02/2022 14:30	16/02/2022 23:30
70	GOL	18/02/2022 18:30	19/02/2022 06:30
71	GOL	19/02/2022 18:30	19/02/2022 23:30
72	HRE	04/02/2022 22:00	05/02/2022 06:00
73	HRE	19/02/2022 18:30	20/02/2022 02:30
74	IKI	02/02/2022 14:00	02/02/2022 22:00
75	IKI	04/02/2022 14:00	04/02/2022 22:00
76	IVA	02/02/2022 22:00	03/02/2022 10:00
77	IVA	04/02/2022 22:00	05/02/2022 06:00
78	IVA	04/02/2022 22:00	05/02/2022 06:00
79	IVA	14/02/2022 06:30	14/02/2022 18:30
80	IVA	15/02/2022 06:30	15/02/2022 18:30
81	IVA	15/02/2022 14:00	15/02/2022 22:00
82	IVA	16/02/2022 06:30	16/02/2022 18:30
83	IVA	16/02/2022 14:00	16/02/2022 22:00
84	IVA	18/02/2022 18:30	19/02/2022 06:30
85	IVK	14/02/2022 06:30	14/02/2022 17:30
86	IVK	15/02/2022 06:30	15/02/2022 18:30
87	IVK	16/02/2022 06:30	16/02/2022 18:30
88	JAD	04/02/2022 22:00	05/02/2022 06:00
89	JAD	14/02/2022 07:00	14/02/2022 15:00
90	JOS	14/02/2022 07:30	14/02/2022 15:30
91	JOS	15/02/2022 07:30	15/02/2022 15:30
92	JOS	16/02/2022 07:30	16/02/2022 15:30
93	JOV	14/02/2022 07:30	14/02/2022 15:30
94	JOV	15/02/2022 07:30	15/02/2022 15:30
95	JOV	16/02/2022 07:30	16/02/2022 15:30
96	KAS	04/02/2022 22:00	05/02/2022 06:00
97	KAS	14/02/2022 06:30	14/02/2022 18:30
98	KAS	15/02/2022 06:30	15/02/2022 18:30
99	KAS	16/02/2022 06:30	16/02/2022 18:30
100	KNE	04/02/2022 22:00	05/02/2022 06:00
101	KNE	14/02/2022 06:30	14/02/2022 18:30
102	KNE	15/02/2022 06:30	15/02/2022 18:30
103	KNE	16/02/2022 06:30	16/02/2022 18:30
104	KOL	02/02/2022 14:00	02/02/2022 22:00
105	KOL	04/02/2022 14:00	04/02/2022 22:00
106	KOL	14/02/2022 06:00	14/02/2022 14:00
107	KOL	15/02/2022 06:00	15/02/2022 14:00
108	KOL	16/02/2022 06:00	16/02/2022 14:00
109	KOL	19/02/2022 06:00	19/02/2022 14:00
110	KRS	04/02/2022 07:30	04/02/2022 15:30
111	KRS	13/02/2022 14:30	13/02/2022 22:30
112	KRS	14/02/2022 07:30	14/02/2022 15:30
113	KRS	15/02/2022 07:30	15/02/2022 15:30
114	KRS	16/02/2022 07:30	16/02/2022 15:30
115	KRU	04/02/2022 22:00	05/02/2022 06:00
116	KRU	14/02/2022 06:30	14/02/2022 18:30
117	KRU	15/02/2022 06:30	15/02/2022 18:30
118	KRU	16/02/2022 06:30	16/02/2022 18:30
119	KUZ	14/02/2022 06:30	14/02/2022 18:30
120	KUZ	15/02/2022 06:30	15/02/2022 18:30
121	LIT	04/02/2022 22:00	05/02/2022 06:00
122	LIT	14/02/2022 06:30	14/02/2022 18:30
123	LIT	15/02/2022 06:30	15/02/2022 18:30
124	LIT	16/02/2022 06:30	16/02/2022 18:30
125	LUK	19/02/2022 18:30	20/02/2022 02:30
126	MAH	13/02/2022 14:30	13/02/2022 22:30
127	MAH	14/02/2022 14:30	14/02/2022 23:30
128	MAH	15/02/2022 14:30	15/02/2022 23:30
129	MAH	16/02/2022 14:30	16/02/2022 23:30
130	MAR	04/02/2022 22:00	05/02/2022 06:00
131	MAR	04/02/2022 22:00	05/02/2022 06:00
132	MAR	14/02/2022 06:00	14/02/2022 14:00
133	MAR	14/02/2022 06:30	14/02/2022 18:30
134	MAR	15/02/2022 06:00	15/02/2022 14:00
135	MAR	15/02/2022 06:30	15/02/2022 18:30
136	MAR	16/02/2022 06:30	16/02/2022 18:30
137	MAR	19/02/2022 06:30	19/02/2022 18:00
138	MAR	21/02/2022 06:00	21/02/2022 14:00
139	PAN	14/02/2022 09:00	14/02/2022 21:00
140	PAN	15/02/2022 14:30	15/02/2022 23:30
141	PAN	16/02/2022 14:30	16/02/2022 23:30
142	PAN	18/02/2022 18:30	19/02/2022 06:30
143	PAN	19/02/2022 18:30	19/02/2022 23:30
144	PAV	14/02/2022 14:30	14/02/2022 23:30
145	PAV	15/02/2022 14:30	15/02/2022 23:30
146	PAV	16/02/2022 14:30	16/02/2022 23:30
147	PAV	18/02/2022 18:30	19/02/2022 06:30
148	PAV	19/02/2022 18:30	20/02/2022 02:30
149	POL	04/02/2022 22:00	05/02/2022 06:00
150	POL	19/02/2022 06:30	19/02/2022 18:00
151	PRO	13/02/2022 14:30	13/02/2022 22:30
152	RAD	04/02/2022 22:00	05/02/2022 06:00
153	RAD	13/02/2022 14:30	13/02/2022 22:30
154	RAD	14/02/2022 09:00	14/02/2022 21:00
155	RAD	14/02/2022 14:30	14/02/2022 23:30
156	RAD	15/02/2022 14:30	15/02/2022 23:30
157	RAD	15/02/2022 14:30	15/02/2022 23:30
158	RAD	16/02/2022 14:30	16/02/2022 23:30
159	RAD	18/02/2022 18:30	19/02/2022 06:30
160	RAD	18/02/2022 18:30	19/02/2022 06:30
161	RAD	19/02/2022 18:30	19/02/2022 23:30
162	RAD	19/02/2022 18:30	20/02/2022 02:30
163	SHU	04/02/2022 22:00	05/02/2022 06:00
164	SHU	14/02/2022 06:30	14/02/2022 18:30
165	SHU	15/02/2022 06:30	15/02/2022 18:30
166	SHU	16/02/2022 06:30	16/02/2022 16:30
167	SKA	04/02/2022 22:00	05/02/2022 06:00
168	SKA	13/02/2022 02:30	13/02/2022 11:00
169	SKA	14/02/2022 06:30	14/02/2022 18:30
170	SKA	15/02/2022 06:30	15/02/2022 18:30
171	SKA	16/02/2022 06:30	16/02/2022 16:30
172	SPA	13/02/2022 14:30	13/02/2022 22:30
173	SPA	14/02/2022 14:30	14/02/2022 23:30
174	SPA	15/02/2022 14:30	15/02/2022 23:30
175	SPA	18/02/2022 18:30	19/02/2022 06:30
176	STE	14/02/2022 09:00	14/02/2022 21:00
177	STE	15/02/2022 14:30	15/02/2022 23:30
178	STE	18/02/2022 18:30	19/02/2022 06:30
179	STE	19/02/2022 18:30	19/02/2022 23:30
180	STO	04/02/2022 22:00	05/02/2022 06:00
181	STO	14/02/2022 06:30	14/02/2022 18:30
182	STO	15/02/2022 06:30	15/02/2022 18:30
183	STO	16/02/2022 06:30	16/02/2022 16:30
184	STO	18/02/2022 18:30	19/02/2022 06:30
185	STO	19/02/2022 18:30	19/02/2022 23:30
186	STR	04/02/2022 07:00	04/02/2022 15:00
187	STR	14/02/2022 06:00	14/02/2022 14:00
188	STR	15/02/2022 06:00	15/02/2022 14:00
189	STR	16/02/2022 06:00	16/02/2022 14:00
190	STR	19/02/2022 06:30	19/02/2022 18:00
191	TAJ	04/02/2022 22:00	05/02/2022 06:00
192	TAJ	14/02/2022 06:30	14/02/2022 15:30
193	TAJ	15/02/2022 06:30	15/02/2022 18:30
194	TAJ	16/02/2022 06:30	16/02/2022 18:30
195	TOM	04/02/2022 22:00	05/02/2022 06:00
196	TOM	14/02/2022 06:30	14/02/2022 18:30
197	TOM	15/02/2022 06:30	15/02/2022 18:30
198	TOM	16/02/2022 06:30	16/02/2022 18:30
199	TSV	04/02/2022 22:00	05/02/2022 06:00
200	TSV	13/02/2022 02:00	13/02/2022 11:00
201	TSV	14/02/2022 06:30	14/02/2022 18:30
202	TSV	15/02/2022 06:30	15/02/2022 18:30
203	TSV	16/02/2022 06:30	16/02/2022 18:30
204	VAL	04/02/2022 22:00	05/02/2022 06:00
205	VAL	13/02/2022 02:00	13/02/2022 11:00
206	VAL	14/02/2022 06:30	14/02/2022 18:30
207	VAL	15/02/2022 14:00	15/02/2022 22:00
208	VAL	16/02/2022 14:00	16/02/2022 22:00
209	VAL	18/02/2022 18:30	19/02/2022 06:30
210	VIN	14/02/2022 14:00	14/02/2022 22:00
211	VIN	19/02/2022 06:30	19/02/2022 18:00
212	VIN	21/02/2022 06:00	21/02/2022 14:00
213	VLA	04/02/2022 22:00	05/02/2022 06:00
214	VLA	13/02/2022 02:00	13/02/2022 11:00
215	VLA	14/02/2022 06:30	14/02/2022 18:30
216	VLA	15/02/2022 06:30	15/02/2022 18:30
217	VLA	16/02/2022 06:30	16/02/2022 18:30
218	VLA	19/02/2022 18:30	20/02/2022 02:30
219	VUK	04/02/2022 07:30	04/02/2022 15:30
220	VUK	14/02/2022 07:00	14/02/2022 15:00
221	VUK	15/02/2022 07:00	15/02/2022 14:00
222	VUK	16/02/2022 06:00	16/02/2022 14:00
223	VUK	19/02/2022 06:30	19/02/2022 18:00
224	ZER	13/02/2022 02:00	13/02/2022 11:00
225	ZER	14/02/2022 06:30	14/02/2022 18:30
226	ZER	15/02/2022 06:30	15/02/2022 18:30
227	ZER	16/02/2022 06:30	16/02/2022 18:30
228	ZUL	04/02/2022 06:30	04/02/2022 16:30
229	ZUL	14/02/2022 09:00	14/02/2022 21:00
230	ZUL	15/02/2022 14:30	15/02/2022 23:30
231	ZUL	18/02/2022 18:30	19/02/2022 06:30
232	ZUL	19/02/2022 18:30	19/02/2022 23:30
233	ZUN	04/02/2022 22:00	05/02/2022 06:00
234	ZUN	14/02/2022 14:00	14/02/2022 22:00
H P D

 

[jtakw]

Hi,

I don't really understand your question, do you just want the hours per row?

Book3.xlsx
	A	B	C	D
1	EMPLOYEE	START TIME	END TIME	Hours
2	ACK	2/2/22 10:00 PM	2/3/22 10:00 AM	12
3	ACK	2/4/22 10:00 PM	2/5/22 6:00 AM	8
4	ACK	2/14/22 6:30 AM	2/14/22 6:30 PM	12
5	ACK	2/15/22 6:30 AM	2/15/22 6:30 PM	12
6	ACK	2/16/22 6:30 AM	2/16/22 4:30 PM	10
7	ATA	2/2/22 10:00 PM	2/3/22 10:00 AM	12
8	ATA	2/4/22 10:00 PM	2/5/22 6:00 AM	8
9	ATA	2/14/22 6:30 AM	2/14/22 6:30 PM	12
10	ATA	2/15/22 6:30 AM	2/15/22 6:30 PM	12
11	ATA	2/16/22 6:30 AM	2/16/22 6:30 PM	12
12	AUB	2/2/22 10:00 PM	2/3/22 10:00 AM	12
Sheet1043

Cell Formulas
Range	Formula
D2:D12	D2	=(C2-B2)*24

 

[Vommy]

Hello,

I divided Worksheet data to steps to clarify my problem. Given data is StartTime and EndTime, Working Hours calculation per Working Day is straight forward.

Scenario: Calculate weekly Working Hours (Monday to Sunday) per calendar date and Monthly Working Hours per calendar month. As I mentioned in my initial post calendar week starts on Monday at 00.00 and ends on Sunday at 23.59.59, month of February starts on 01/02/2022 at 00:00 and ends on 28/02/2022 at 23:59:59.

Uploaded Worksheet contains February roster example for one employee.

Problem: When shift starts on 31/01/2022 at 18.00 and ends on 01/02/2022 at 06:00 we have 6 working hours in January and 6 working hours in February the latter should be included in February monthly working hours, it equally applies to shift on 28/02/2022 from 21:00 to 09:00 on 01/03/2022, when 3 working hours from 21:00 until midnight should be included in February working hours.
Similarly it is relevant to weekly working hours when shift starts on Sunday and ends on Monday. Sunday working hours should be included in the week ending at midnight and Monday working hours (after midnight) should be calculated to the following week.

Pivot table monthly working hours (or 7-day grouping starting Monday for weekly hours) presents data by starting date taking into account entire shift Ex. on 31/01/2022 12 working hours despite the fact
that actual working hours in January were 6.

Question: Aforementioned goal would be achieved with working hours calculation daily by calendar date (from 00:00 to 23:59:59). How to fulfill it?

EMPLOYEE	Weekday	START TIME	END TIME	Time Worked	February Hours	Weekly Hours
ACK	Monday	31/01/2022 18:00​	01/02/2022 06:00​	12.00	6.00
ACK	Tuesday	01/02/2022 22:00​	02/02/2022 06:00​	8.00	8.00
ACK	Thursday	03/02/2022 12:00​	03/02/2022 20:00​	8.00	8.00
ACK	Friday	04/02/2022 12:00​	04/02/2022 20:00​	8.00	8.00
ACK	Saturday	05/02/2022 12:00​	05/02/2022 20:00​	8.00	8.00
ACK	Sunday	06/02/2022 22:00​	07/02/2022 10:00​	12.00	12.00	50.00
ACK	Tuesday	08/02/2022 08:00​	08/02/2022 16:00​	8.00	8.00
ACK	Wednesday	09/02/2022 08:00​	09/02/2022 16:00​	8.00	8.00
ACK	Thursday	10/02/2022 08:00​	10/02/2022 16:00​	8.00	8.00
ACK	Friday	11/02/2022 08:00​	11/02/2022 16:00​	8.00	8.00
ACK	Sunday	13/02/2022 22:00​	14/02/2022 10:00​	12.00	12.00	50.00
ACK	Tuesday	15/02/2022 10:00​	15/02/2022 18:00​	8.00	8.00
ACK	Wednesday	16/02/2022 10:00​	16/02/2022 18:00​	8.00	8.00
ACK	Thursday	17/02/2022 10:00​	17/02/2022 18:00​	8.00	8.00
ACK	Friday	18/02/2022 10:00​	18/02/2022 18:00​	8.00	8.00
ACK	Sunday	20/02/2022 19:00​	21/02/2022 07:00​	12.00	12.00	49.00
ACK	Tuesday	22/02/2022 10:00​	22/02/2022 18:00​	8.00	8.00
ACK	Wednesday	23/02/2022 10:00​	23/02/2022 18:00​	8.00	8.00
ACK	Thursday	24/02/2022 10:00​	24/02/2022 18:00​	8.00	8.00
ACK	Friday	25/02/2022 10:00​	25/02/2022 18:00​	8.00	8.00
ACK	Saturday	26/02/2022 10:00​	26/02/2022 18:00​	8.00	8.00
ACK	Sunday	27/02/2022 18:00​	28/02/2022 06:00​	12.00	12.00	53.00
ACK	Monday	28/02/2022 21:00​	01/03/2022 09:00​	12.00	3.00

 

[jtakw]

Thanks for clarifying, it helps a bit.
Your expected results in your Weekly Hours column seems incorrect, the last 3 totals don't add up.
This is what I have, hope it helps:

Book3.xlsx
	A	B	C	D	E	F	G
1	EMPLOYEE	Weekday	START TIME	END TIME	Time Worked	February Hours	Weekly Hours
2	ACK	Monday	1/31/22 18:00	2/1/22 6:00	12	6
3	ACK	Tuesday	2/1/22 22:00	2/2/22 6:00	8	8
4	ACK	Thursday	2/3/22 12:00	2/3/22 20:00	8	8
5	ACK	Friday	2/4/22 12:00	2/4/22 20:00	8	8
6	ACK	Saturday	2/5/22 12:00	2/5/22 20:00	8	8
7	ACK	Sunday	2/6/22 22:00	2/7/22 10:00	12	12	50
8	ACK	Tuesday	2/8/22 8:00	2/8/22 16:00	8	8
9	ACK	Wednesday	2/9/22 8:00	2/9/22 16:00	8	8
10	ACK	Thursday	2/10/22 8:00	2/10/22 16:00	8	8
11	ACK	Friday	2/11/22 8:00	2/11/22 16:00	8	8
12	ACK	Sunday	2/13/22 22:00	2/14/22 10:00	12	12	44
13	ACK	Tuesday	2/15/22 10:00	2/15/22 18:00	8	8
14	ACK	Wednesday	2/16/22 10:00	2/16/22 18:00	8	8
15	ACK	Thursday	2/17/22 10:00	2/17/22 18:00	8	8
16	ACK	Friday	2/18/22 10:00	2/18/22 18:00	8	8
17	ACK	Sunday	2/20/22 19:00	2/21/22 7:00	12	12	44
18	ACK	Tuesday	2/22/22 10:00	2/22/22 18:00	8	8
19	ACK	Wednesday	2/23/22 10:00	2/23/22 18:00	8	8
20	ACK	Thursday	2/24/22 10:00	2/24/22 18:00	8	8
21	ACK	Friday	2/25/22 10:00	2/25/22 18:00	8	8
22	ACK	Saturday	2/26/22 10:00	2/26/22 18:00	8	8
23	ACK	Sunday	2/27/22 18:00	2/28/22 6:00	12	12	52
24	ACK	Monday	2/28/22 21:00	3/1/22 9:00	12	3
Sheet1043

Cell Formulas
Range	Formula
F2:F24	F2	=IF(OR(YEAR(C2)<YEAR(D2),MONTH(C2)<MONTH(("1/"&LEFT(F$1,FIND(" ",F$1)))+0)),HOUR(D2),IF(OR(YEAR(D2)>YEAR(C2),MONTH(D2)>MONTH(("1/"&LEFT(F$1,FIND(" ",F$1)))+0)),24-HOUR(C2),(D2-C2)*24))
G2:G24	G2	=IF(B2="Sunday",SUM(F$2:F2)-SUM(G$1:G1),"")

 

[Sulprobil]

Hello,

I suggest to look at this thread:

Shifts and bonuses

Hi, I have a problem I have been trying to find a solution to for a while now. We have people that work shifts. Bonuses can be received for each shift depending on time and day. ---**disclaimer** this is not the actual bonus system, since we don't even use dollars in my country but it's just...

www.mrexcel.com

Regards,
Bernd