How to Split Shifts into Day/Night, Weekends

[WaheedIqbal]

Dear Excel experts,

I am required to make payroll payment to few (10-15) employees working in Security industry (Shift workers). The pays are determined by the timings of the shift i.e different rates of pay for Day shift (06:00 to 18:00), night shift (06:00 to 18:00), Saturday (24 hrs) and Sunday (24 hrs).

Please see sample data from the manual working i have done:
[TABLE="class: grid, width: 641"]
<colgroup><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Shift[/TD]
[TD]Start Time[/TD]
[TD]Finish Time[/TD]
[TD]Hours[/TD]
[TD]Day[/TD]
[TD]Night[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD="align: right"]14-03-18[/TD]
[TD]Wednesday[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]0:00[/TD]
[TD] 4.00[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl65, width: 68, align: right"]16-03-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Friday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl65, width: 70, align: right"]22:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"]2:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD="class: xl65, width: 49"] 4.25 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74, align: right"]2.25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl65, width: 68, align: right"]17-03-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Saturday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl65, width: 70, align: right"]15:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"]4:30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD="class: xl65, width: 49"] 13.50 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74, align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl65, width: 74, align: right"]4.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The first four columns is what i get from my operations team and i then manually split the shifts into Day/Night/Sat/Sun.

Is there a way that this can be automated?

Many thanks for your help.

Regards,

Waheed

 

[Ingolf]

Change O2 to =N2+1 and re-check the result of my formula.

@jorismoerings,

This was!!!
You have not specified in the posts before, and in N2 and O2 it is clear 00:00:00

P.S.
I tested your formulas in excel 2010, 2013, 2016 and excel in office 365 and in all versions I gave 0 (zero).
After changing the O2 cell in = N2 + 1 it's OK now.

 

[WaheedIqbal]

There you go. Got it. =o2=n2 was true and changing it as you mentioned above has solved the issue.

Bundle of thanks for your time Joris. You the champ.

Best Regards,
Waheed

 

[KIERA]

Hello,
I am trying to use the above spreadsheet to calculate hours at night, weekends etc
When I ;paste the formula F2 I get an error saying there is something wrong with the formula. This is Office 365 and i have enabled the analysis pack add in.
Any idea why i might be getting an error ?
Many thanks.

 

[jorismoerings]

Hello,
I am trying to use the above spreadsheet to calculate hours at night, weekends etc
When I ;paste the formula F2 I get an error saying there is something wrong with the formula. This is Office 365 and i have enabled the analysis pack add in.
Any idea why i might be getting an error ?
Many thanks.

hi Kiera

could you show more details of the error? Does it come with a not expected answer, does it show #n/a, or error or …

 

[KIERA]

Hello amd thanks for the reply.
I have pasted a minisheet below. Also a picture of the error.
I have also posted an example minisheet of what i am trying to achieve (will send in next post).
Hope you can help!
Many thanks
Kier.

Hours calcs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			DATE	SHIFT	START TIME	FINISHI TIME	HOURS	DAY	NIGHT	SAT	SUN		DAY SHIFT	06:00:00	18:00:00
2			11/05/2018	FRIDAY	22:00:00	02:00:00	00:00	=IFERROR(((NETWORKDAYS.INTL((C2+E2),(C2+(F2)+F2),"0000011",)-1)*($O$1-$N$1)+IF(NETWORKDAYS.INTL((IF(F2)+F2),(IF(F2)+F2),"0000011"),MEDIAN(MOD((IF(F2)+F2),1),$O$1,$N$1),$O$1)-MEDIAN(NETWORKDAYS.INTL((C2+E2),(C2+E2),"0000011")*MOD((C2+E2),1),$O$1,$N$1))*24,0)	#VALUE!	=IFERROR(((NETWORKDAYS.INTL((C2+E2),(IF(F2)+F2),"1111101")-1)*($O$2-$N$2)+IF(NETWORKDAYS.INTL((IF(F2)+F2),(IF(F2)+F2),"1111101"),MEDIAN(MOD((IF(F2)+F2),1),$O$2,$N$2),$O$2)-MEDIAN(NETWORKDAYS.INTL((C2+E2),(C2+E2),"1111101")*MOD((C2+E2),1),$O$2,$N$2))*24,0)	=IFERROR(((NETWORKDAYS.INTL((C2+E2),(IF(F2)+F2),"1111110")-1)*($O$2-$N$2)+IF(NETWORKDAYS.INTL((IF(F2)+F2),(IF(F2)+F2),"1111110"),MEDIAN(MOD((IF(F2)+F2),1),$O$2,$N$2),$O$2)-MEDIAN(NETWORKDAYS.INTL((C2+E2),(C2+E2),"1111110")*MOD((C2+E2),1),$O$2,$N$2))*24,0)		SAT /SUN	00:00:00	00:00:00
3			12/05/2018	SATURDAY	23:00	03:00	00:00	=IFERROR(((NETWORKDAYS.INTL((C2+E2),(C2+(F2)+F2),"0000011",)-1)*($O$1-$N$1)+IF(NETWORKDAYS.INTL((IF(F2)+F2),(IF(F2)+F2),"0000011"),MEDIAN(MOD((IF(F2)+F2),1),$O$1,$N$1),$O$1)-MEDIAN(NETWORKDAYS.INTL((C2+E2),(C2+E2),"0000011")*MOD((C2+E2),1),$O$1,$N$1))*24,0)	0
4			11/05/2018	FRIDAY	22:00	02:00	00:00	=IFERROR(((NETWORKDAYS.INTL((C2+E2),(C2+(F2)+F2),"0000011",)-1)*($O$1-$N$1)+IF(NETWORKDAYS.INTL((IF(F2)+F2),(IF(F2)+F2),"0000011"),MEDIAN(MOD((IF(F2)+F2),1),$O$1,$N$1),$O$1)-MEDIAN(NETWORKDAYS.INTL((C2+E2),(C2+E2),"0000011")*MOD((C2+E2),1),$O$1,$N$1))*24,0)	#VALUE!
5
6
Sheet1

Cell Formulas
Range	Formula
O2	O2	=N2+1
G2:G4	G2	=SUM(F2:I2)
I2:I4	I2	=IF(WEEKDAY(C2,2)<6,(((C2+IF(AND(WEEKDAY(C2,2)=5,F2),$O$2,F2))-(C2+E2)+((C2+IF(AND(WEEKDAY(C2,2)=5,F2),$O$2,F2))<(C2+E2)))*24)-H2,0)

 

[KIERA]

Export - 2022-05-02T140127.666.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Timesheets Previously Released						Hours day (Week day)	Hours Night (excluding weekends)	Hours Saturday	Hours Sunday	Hours Bank Holiday
2	Date	Actual					0600-2000	2000-0600	0000-0000	0000-0000	0000-0000
3		Start	End	Break In Minutes	Total
4	11/04/2022	19:45	07:45	45	11:15
5
6	11/04/2022	06:45	20:45	60	13:00
7
8	11/04/2022	21:00	08:00	45	10:15
9
10	11/04/2022	08:00	20:00	30	11:30
11
12	11/04/2022	20:00	08:00	45	11:15
13
14	11/04/2022	08:30	16:30	30	07:30
15
16	11/04/2022	09:00	17:00	30	07:30
17
18	11/04/2022	08:00	20:00	30	11:30
19
20	11/04/2022	09:00	17:00	30	07:30
21
22	11/04/2022	09:00	17:00	30	07:30
23
24	11/04/2022	09:00	17:00	30	07:30
25
Export - 2022-05-02T140127.666

 

[KIERA]

hi Kiera

could you show more details of the error? Does it come with a not expected answer, does it show #n/a, or error or …

Hey... dont suppose you have any thoughts on the info I sent yesterday?
BW
Kier.

 

[jorismoerings]

Nope sorry, it’s been busy at work. Will try to look at it tomorrow.