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:

Date	Shift	Start Time	Finish Time	Hours	Day	Night	Sat	Sun
14-03-18	Wednesday	20:00	0:00	4.00		4
16-03-18 <tbody> </tbody>	Friday <tbody> </tbody>	22:00 <tbody> </tbody>	2:15 <tbody> </tbody>	4.25 <tbody> </tbody>		2 <tbody> </tbody>	2.25 <tbody> </tbody>
17-03-18 <tbody> </tbody>	Saturday <tbody> </tbody>	15:00 <tbody> </tbody>	4:30 <tbody> </tbody>	13.50 <tbody> </tbody>			9 <tbody> </tbody>	4.5 <tbody> </tbody>

<colgroup><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

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

 

[jorismoerings]

Hi Waheed, and welcome to MrExcel,

Take a look at this and see if this helps:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Shift	Start Time	Finish Time	Hours	Day	Night	Sat	Sun		Day Shift	06:00:00	18:00:00
2	14-Mar-18	Wednesday	20:00	00:00	4,00	-	4,00	0	0
3	16-Mar-18	Friday	22:00	02:15	4,25	-	4,25	0	0
4	17-Mar-18	Saturday	15:00	04:30	13,50	-	-	13,5	0
Sheet1

Cell Formulas
Range	Formula
E2	=SUM(F2:I2)
F2	=IFERROR(((NETWORKDAYS.INTL((A2+C2),(IF(D2)+D2),"0000011",)-1)*($M$1-$L$1)+IF(NETWORKDAYS.INTL((IF(D2)+D2),(IF(D2)+D2),"0000011"),MEDIAN(MOD((IF(D2)+D2),1),$M$1,$L$1),$M$1)-MEDIAN(NETWORKDAYS.INTL((A2+C2),(A2+C2),"0000011")*MOD((A2+C2),1),$M$1,$L$1))*24,0)
G2	=IF(WEEKDAY(A2,2)<6,(((A2+D2)-(A2+C2)+((A2+D2)<(A2+C2)))*24)-F2,0)
H2	=IF(WEEKDAY(A2,2)=6,(((A2+D2)-(A2+C2)+((A2+D2)<(A2+C2)))*24),0)
I2	=IF(WEEKDAY(A2,2)=7,(((A2+D2)-(A2+C2)+((A2+D2)<(A2+C2)))*24),0)

 

[WaheedIqbal]

Hi Joris

Thank you for your time and apologies for a delayed response.

I have tried the above and got following results: (sorry couldn't figure out how to paste excel sheet)

Date	Shift	Start Time	Finish Time	Hours	Day	Night	Sat	Sunday
11-05-18	Friday	22:00	2:00	4	-22	26	0	0
12-05-18	Saturday	23:00	3:00	4	0	0	4	0
11-05-18	Friday	22:00	2:00	4	-22	26	0	0
13-05-18	Sunday	0:00	4:30	4.5	0	0	0	4.5
12-05-18	Saturday	23:00	3:00	4	0	0	4	0
09-05-18	Wednesday	20:00	1:15	5.25	-18.75	24	0	0
11-05-18	Friday	21:00	1:45	4.75	-21	25.75	0	0
12-05-18	Saturday	21:00	4:15	7.25	0	0	7.25	0
12-05-18	Saturday	22:00	3:00	5	0	0	5	0

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>

As you can see, some adjustment to formula is required. the first row result should have been 2 hours under Friday and 2 under Saturday (changed from Friday to Saturday after midnight). Similarly, 2nd row result should have been 1 hour Saturday and 3 hour sunday. Can you please have a look.

Also I have client and employee name in first two columns A & B of the sheet. Can you please adjust the formulas for this.

Hope i have been able to explain. Many thanks again for your help.
Much appreciated.

 

[jorismoerings]

Hi,

try this:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Shift	Start Time	Finish Time	Hours	Day	Night	Sat	Sun		Day Shift	06:00:00	18:00:00
2	11-5-2018	Friday	22:00	02:00	4,00	0,00	2,00	2,00	0,00		Sat/Sun	00:00:00	00:00:00
3	12-5-2018	Saturday	23:00	03:00	4,00	0,00	0,00	1,00	3,00
4	11-5-2018	Friday	22:00	02:00	4,00	0,00	2,00	2,00	0,00
5	13-5-2018	Sunday	00:00	04:30	4,50	0,00	0,00	0,00	4,50
6	12-5-2018	Saturday	23:00	03:00	4,00	0,00	0,00	1,00	3,00
7	9-5-2018	Wednesday	20:00	01:15	5,25	0,00	5,25	0,00	0,00
8	11-5-2018	Friday	21:00	01:45	4,75	0,00	3,00	1,75	0,00
9	12-5-2018	Saturday	21:00	04:15	7,25	0,00	0,00	3,00	4,25
10	12-5-2018	Saturday	22:00	03:00	5,00	0,00	0,00	2,00	3,00
Sheet1

Cell Formulas
Range	Formula
H2	=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)
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)
J2	=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)
K2	=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)

 

[WaheedIqbal]

HI Mate

Got this result which still is not correct. the total actual hours in the first shift are 4 whereas the formula result is only under "Night". There should be 2 under Sat also. the result in 2nd row is 0 only.

Date Shift Start Time Finish Time Actual Hrs Day Night Saturday Sunday Day Shift 6:00:00 18:00 11-05-18 Friday 22:00 2:00 4.00 0 2 0 0 Sat/Sun 0:00 0:00 12-05-18 Saturday 23:00 3:00 4.00 0 0 0 0 11-05-18 Friday 22:00 2:00 4.00 0 2 0 0 13-05-18 Sunday 0:00 4:30 4.50 0 0 0 0 <colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

Can you please have a look.

Thanks

 

[jorismoerings]

Hi,

taken a quick look but i don't see anything being off.
i redid my example and it's clearly showing 2 under saturday in cell J2.
So don't know what you've did wrong but you didn't copy my formula exactly.

	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Shift	Start Time	Finish Time	Hours	Day	Night	Sat	Sun		Day Shift	06:00:00	18:00:00
2	11-5-2018	Friday	22:00	02:00	4,00	-	2,00	2,00	-		Sat/Sun	00:00:00	00:00:00
3	12-5-2018	Saturday	23:00	03:00	4,00	-	-	1,00	3,00
4	11-5-2018	Friday	22:00	02:00	4,00	-	2,00	2,00	-
5	13-5-2018	Sunday	00:00	04:30	4,50	-	-	-	4,50
6	12-5-2018	Saturday	23:00	03:00	4,00	-	-	1,00	3,00
7	9-5-2018	Wednesday	20:00	01:15	5,25	-	5,25	-	-
8	11-5-2018	Friday	21:00	01:45	4,75	-	3,00	1,75	-
9	12-5-2018	Saturday	21:00	04:15	7,25	-	-	3,00	4,25
10	12-5-2018	Saturday	22:00	03:00	5,00	-	-	2,00	3,00
11	11-5-2018	Friday	08:00	02:00	18,00	10,00	6,00	2,00	-

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[WaheedIqbal]

Hi Joris

Sorry to be a pain. Tried the exact copy of your screen shot and full copy paste of formulas. I am still getting problem with Saturday/Sunday. Do i need a specific format for the extra columns M, N and O you have inserted or may be AM/PM issue? Also is it possible we can exchange excel sheet?

Thanks

 

[Ingolf]

Hi,

@jorismoerings,

Your formula in #4 don't gave me same result as is in your post.
For Sat and Sun I got only 0 (zero)

 

[jorismoerings]

@Ingolf @WaheedIqbal

Just hoping you both noticed the small differences in the formula for weekdays and saturday and sunday but assuming you did and subsequently assuming you've got the "analysis pack" which holds the "networkdays.intl" function turned on.

Just to check assumptions:
1. Can you confirm you've turned on the "analysis pack" to be abvle to use the NETWORKDAYS.INTL function
2. Can you share you're Excel version
3. Could you share a screenshot/or an example file with your attempts so i can take a look.

 

[jorismoerings]

@Ingolf @WaheedIqbal

Besides forementioned assumptions: there's a difference in the cell N2 and O2 which is important and i forgot to mention.

Can you do this check =O2=N2. If the result comes back with TRUE. Change O2 to =N2+1 and re-check the result of my formula.