Calc Finish Date from known Start Date and Duration across 2 shifts on different Workdays

[PGC14]

I am building a Planning Model for a manufacturing company and need to calculate the Finish Date and Time from a known Start Date, Time and Job Duration. The complicating thing here is that they have 2 shifts - Dayshift work Monday to Friday, and Nightshift Monday to Thursday. Although Friday is not a workday for Nightshift the formula needs to use the dayshift hours for the Friday (in addition to the hours Monday to Thursday) to calculate the correct Finish Date and Time.

I am using the NETWORKDAY.INTL formula and specifying the working days in the array. I have used "000011" for Dayshift and "0000111" for Nightshift.

I have only been able to use one or the other in my development of the formula but when using the Nightshift specified days, it assumes there is no work on the Friday which is incorrect as Dayshift work all of Friday.

MY attached worksheet shows various examples and the results in column E. Note that in E6 I have changed the specified working days in the array to reflect the 4 working days for Nightshift, but when I do this it shifts the Finish date to the 4th. If I leave the array as per dayshift, it assumes Nightshift work on the Friday Night which is incorrect.

Planning Model April 2022.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	PackingStartDate	Packing Start Time	StartDateandTime	Packing Duration (Hours)	Calculated Order Finish Date/Time	This is what it should return			NETWORKDAYS.INTL specified working days array
3	31/03/2022	7:00	31/03/2022 07:00	2.00	31/03/2022 9:00	31/03/2022 9:00	Duration consumed only Dayshift Hours		"0000011"	Working Days Monday to Friday
4	31/03/2022	7:00	31/03/2022 07:00	11.00	31/03/2022 18:00	31/03/2022 18:00	Duration consumed all Dayshift Hours and some of Nightshift Hours		"0000011"	Working Days Monday to Friday
5	31/03/2022	7:00	31/03/2022 07:00	20.00	1/04/2022 9:00	1/04/2022 9:00	Duration consumed all Dayshift Hours, all Nightshift Hours, and some of next days Dayshift		"0000011"	Working Days Monday to Friday
6	31/03/2022	7:00	31/03/2022 07:00	20.00	4/04/2022 9:00	1/04/2022 9:00	Duration consumed all Dayshift Hours, all Nightshift Hours but assumed no Dayhsift on Friday so only looked at Monday's Dayhsift hours		"0000111"	Working Days Monday to THURSDAY
7
8
9					It returns this which is incorrect as whilst the Friday is not a Working Day for Nighshift but it is for Dayshift. I want it to consider the Dayshift hours on a Friday to arrive at the correct Order Finish Date and Time
10
11
12	Statutory + Company Non-Working Days	2022		Shift Start and Finish Times
13	Day	Date		Start_Hour_Day	7:00:00 AM
14	Monday	3-Jan-22		Finish_Hour_Day	3:30:00 PM
15	Tuesday	4-Jan-22		Start_Hour_Night	3:30:00 PM
16	Wednesday	5-Jan-22		Finish_Hour_Night	1:00:00 AM
17	Thursday	6-Jan-22
18	Friday	7-Jan-22
19	Monday	31-Jan-22
20	Monday	7-Feb-22
21	Friday	15-Apr-22
22	Monday	18-Apr-22
23	Monday	25-Apr-22
24	Monday	6-Jun-22
25	Friday	24-Jun-22
26	Monday	24-Oct-22
27	Friday	23-Dec-22
28	Monday	26-Dec-22
29	Tuesday	27-Dec-22
30	Wednesday	28-Dec-22
31	Thursday	29-Dec-22
32	Friday	30-Dec-22
33
34
35
36
37
38
Packing

Cell Formulas
Range	Formula
C3:C6	C3	=A3+B3
E3:E4	E3	=(WORKDAY.INTL(C3,CEILING((D3/24+MOD(C3,1)-Start_Hour_Day)/(MOD(Finish_Hour_Night-Start_Hour_Day,1)),1)-1,"0000011",NoWorking))+MOD(C3,1)+D3/24-CEILING((MOD(C3,1)+D3/24-Start_Hour_Day),MOD(Finish_Hour_Night-Start_Hour_Day,1))+MOD(Finish_Hour_Night-Start_Hour_Day,1)
E5	E5	=(WORKDAY.INTL(C5,CEILING((D5/24+MOD(C5,1)-Start_Hour_Day)/(MOD(Finish_Hour_Night-Start_Hour_Day,1)),1)-1,"0000011",No_Working))+MOD(C5,1)+D5/24-CEILING((MOD(C5,1)+D5/24-Start_Hour_Day),MOD(Finish_Hour_Night-Start_Hour_Day,1))+MOD(Finish_Hour_Night-Start_Hour_Day,1)
E6	E6	=(WORKDAY.INTL(C6,CEILING((D6/24+MOD(C6,1)-Start_Hour_Day)/(MOD(Finish_Hour_Night-Start_Hour_Day,1)),1)-1,"0000111",No_Working))+MOD(C6,1)+D6/24-CEILING((MOD(C6,1)+D6/24-Start_Hour_Day),MOD(Finish_Hour_Night-Start_Hour_Day,1))+MOD(Finish_Hour_Night-Start_Hour_Day,1)
A14:A32	A14	=TEXT(B14,"dddd")

Named Ranges
Name	Refers To	Cells
Finish_Hour_Night	=Packing!$E$16	E3:E6
No_Working	=Packing!$B$14:$B$38	E5:E6, A14
Start_Hour_Day	=Packing!$E$13	E3:E6