Formula for Calculating Production Finish Time

[Legacy 143009]

Hi,

Product	Order (pcs.)	Production Time (min./pcs.)	Start Date	Start Time	Finish Date	Finish Time
FA00747	900	240	23.11.2023	19:50

I need 2 formulas to calculate the finish date and time for the given work order.
The rules are:
1. Factory is working in two shifts 08:00-16:00 and 16:00-24:00
2. Factory is not working on Sundays

You may consider the table starting from A2 cell.

Thanks for your support!

 

[felixstraube]

Here is a working example I think:

Production time.xlsx
	A	B	C	D	E	F	G	H	I
1	Product	Order (pcs.)	Production time (min./pcs.)	Start Date	Start Time	Finish Date	Finish Time		Example calendar
2	FA00747	900	240	23/11/2023	19:50	12/08/2024	19:50		23/11/2023 ju
3	TestValue01	1	240	23/11/2023	20:00	24/11/2023	00:00		24/11/2023 vi
4	TestValue02	2	240	23/11/2023	20:00	24/11/2023	12:00		25/11/2023 sá
5	TestValue03	3	240	23/11/2023	20:00	24/11/2023	16:00		26/11/2023 do
6	TestValue04	4	240	23/11/2023	20:00	24/11/2023	20:00		27/11/2023 lu
7	TestValue05	5	240	23/11/2023	20:00	25/11/2023	00:00		28/11/2023 ma
8	TestValue06	6	240	23/11/2023	20:00	25/11/2023	12:00		29/11/2023 mi
9	TestValue07	7	240	23/11/2023	20:00	25/11/2023	16:00		30/11/2023 ju
10	TestValue08	8	240	23/11/2023	20:00	25/11/2023	20:00		01/12/2023 vi
11	TestValue09	9	240	23/11/2023	20:00	26/11/2023	00:00		02/12/2023 sá
12	TestValue10	10	240	23/11/2023	20:00	27/11/2023	12:00		03/12/2023 do
13	TestValue11	11	240	23/11/2023	20:00	27/11/2023	16:00		04/12/2023 lu
14	TestValue12	12	240	23/11/2023	20:00	27/11/2023	20:00		05/12/2023 ma
15	TestValue13	13	240	23/11/2023	20:00	28/11/2023	00:00		06/12/2023 mi
16	TestValue14	14	240	23/11/2023	20:00	28/11/2023	12:00		07/12/2023 ju
17	TestValue15	15	240	23/11/2023	20:00	28/11/2023	16:00		08/12/2023 vi
18	TestValue16	16	240	23/11/2023	20:00	28/11/2023	20:00		09/12/2023 sá
19	TestValue17	17	240	23/11/2023	20:00	29/11/2023	00:00		10/12/2023 do
20	TestValue18	18	240	23/11/2023	20:00	29/11/2023	12:00		11/12/2023 lu
21	TestValue19	19	240	23/11/2023	20:00	29/11/2023	16:00		12/12/2023 ma
22	TestValue20	20	240	23/11/2023	20:00	29/11/2023	20:00		13/12/2023 mi
23	TestValue21	21	240	23/11/2023	20:00	30/11/2023	00:00		14/12/2023 ju
24	TestValue22	22	240	23/11/2023	20:00	30/11/2023	12:00		15/12/2023 vi
25	TestValue23	23	240	23/11/2023	20:00	30/11/2023	16:00		16/12/2023 sá
26	TestValue24	24	240	23/11/2023	20:00	30/11/2023	20:00		17/12/2023 do
27	TestValue25	25	240	23/11/2023	20:00	01/12/2023	00:00		18/12/2023 lu
28	TestValue26	26	240	23/11/2023	20:00	01/12/2023	12:00		19/12/2023 ma
29	TestValue27	27	240	23/11/2023	20:00	01/12/2023	16:00		20/12/2023 mi
Hoja2

Cell Formulas
Range	Formula
F2:F29	F2	=IF(ROUND((((B2*C2/60)+(E2-TIMEVALUE("8:00"))*24)-INT(((B2*C2/60)+(E2-TIMEVALUE("8:00"))*24)/16)*16),5)/24=0,WORKDAY.INTL(D2,INT(((B2*C2/60)+(E2-TIMEVALUE("8:00"))*24)/16)-1,11)+1,WORKDAY.INTL(D2,INT(((B2*C2/60)+(E2-TIMEVALUE("8:00"))*24)/16),11))
G2:G29	G2	=IF(ROUND((((B2*C2/60)+(E2-TIMEVALUE("8:00"))*24)-INT(((B2*C2/60)+(E2-TIMEVALUE("8:00"))*24)/16)*16),5)/24=0,0,ROUND((((B2*C2/60)+(E2-TIMEVALUE("8:00"))*24)-INT(((B2*C2/60)+(E2-TIMEVALUE("8:00"))*24)/16)*16),5)/24+TIMEVALUE("8:00"))
I2	I2	=D2
I3:I29	I3	=I2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I30	Expression	=DIASEM(I2)=1	text	NO

 

[Legacy 143009]

Totally amazing! The result is correct. Thanks for your great effort.

 

[felixstraube]

My pleasure. Thanks for the feedback.

As the formulae are a little complex here is the same calculation but in steps (and how it came to be) for more clarity:

Production time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Product	Order (pcs.)	Production time (min./pcs.)	Start Date	Start Time		Production time (hours)	Production time + start hour	Prod time in Days - hours (not used - just to see the time)	Prod time in days	Prod time remaining hours	Finish Date	Finish Time			Example calendar
2	FA00747	901	240	23/11/2023	19:50		3604	3615,833333	12 15:50	225	15:50:00	12/08/2024	23:50			23/11/2023 ju
3	TestValue01	1	240	23/11/2023	20:00		4	16	1 00:00	1	0:00:00	24/11/2023	00:00			24/11/2023 vi
4	TestValue02	2	240	23/11/2023	20:00		8	20	1 04:00	1	4:00:00	24/11/2023	12:00			25/11/2023 sá
5	TestValue03	3	240	23/11/2023	20:00		12	24	1 08:00	1	8:00:00	24/11/2023	16:00			26/11/2023 do
6	TestValue04	4	240	23/11/2023	20:00		16	28	1 12:00	1	12:00:00	24/11/2023	20:00			27/11/2023 lu
7	TestValue05	5	240	23/11/2023	20:00		20	32	2 00:00	2	0:00:00	25/11/2023	00:00			28/11/2023 ma
8	TestValue06	6	240	23/11/2023	20:00		24	36	2 04:00	2	4:00:00	25/11/2023	12:00			29/11/2023 mi
9	TestValue07	7	240	23/11/2023	20:00		28	40	2 08:00	2	8:00:00	25/11/2023	16:00			30/11/2023 ju
10	TestValue08	8	240	23/11/2023	20:00		32	44	2 12:00	2	12:00:00	25/11/2023	20:00			01/12/2023 vi
11	TestValue09	9	240	23/11/2023	20:00		36	48	3 00:00	3	0:00:00	26/11/2023	00:00			02/12/2023 sá
12	TestValue10	10	240	23/11/2023	20:00		40	52	3 04:00	3	4:00:00	27/11/2023	12:00			03/12/2023 do
13	TestValue11	11	240	23/11/2023	20:00		44	56	3 08:00	3	8:00:00	27/11/2023	16:00			04/12/2023 lu
14	TestValue12	12	240	23/11/2023	20:00		48	60	3 12:00	3	12:00:00	27/11/2023	20:00			05/12/2023 ma
15	TestValue13	13	240	23/11/2023	20:00		52	64	4 00:00	4	0:00:00	28/11/2023	00:00			06/12/2023 mi
16	TestValue14	14	240	23/11/2023	20:00		56	68	4 04:00	4	4:00:00	28/11/2023	12:00			07/12/2023 ju
17	TestValue15	15	240	23/11/2023	20:00		60	72	4 08:00	4	8:00:00	28/11/2023	16:00			08/12/2023 vi
18	TestValue16	16	240	23/11/2023	20:00		64	76	4 12:00	4	12:00:00	28/11/2023	20:00			09/12/2023 sá
19	TestValue17	17	240	23/11/2023	20:00		68	80	5 00:00	5	0:00:00	29/11/2023	00:00			10/12/2023 do
20	TestValue18	18	240	23/11/2023	20:00		72	84	5 04:00	5	4:00:00	29/11/2023	12:00			11/12/2023 lu
21	TestValue19	19	240	23/11/2023	20:00		76	88	5 08:00	5	8:00:00	29/11/2023	16:00			12/12/2023 ma
22	TestValue20	20	240	23/11/2023	20:00		80	92	5 12:00	5	12:00:00	29/11/2023	20:00			13/12/2023 mi
23	TestValue21	21	240	23/11/2023	20:00		84	96	6 00:00	6	0:00:00	30/11/2023	00:00			14/12/2023 ju
24	TestValue22	22	240	23/11/2023	20:00		88	100	6 04:00	6	4:00:00	30/11/2023	12:00			15/12/2023 vi
25	TestValue23	23	240	23/11/2023	20:00		92	104	6 08:00	6	8:00:00	30/11/2023	16:00			16/12/2023 sá
26	TestValue24	24	240	23/11/2023	20:00		96	108	6 12:00	6	12:00:00	30/11/2023	20:00			17/12/2023 do
27	TestValue25	25	240	23/11/2023	20:00		100	112	7 00:00	7	0:00:00	01/12/2023	00:00			18/12/2023 lu
28	TestValue26	26	240	23/11/2023	20:00		104	116	7 04:00	7	4:00:00	01/12/2023	12:00			19/12/2023 ma
29	TestValue27	27	240	23/11/2023	20:00		108	120	7 08:00	7	8:00:00	01/12/2023	16:00			20/12/2023 mi
Hoja1

Cell Formulas
Range	Formula
G2:G29	G2	=B2*C2/60
H2:H29	H2	=G2+(E2-TIMEVALUE("8:00"))*24
I2:I29	I2	=INT(H2/16)+(H2-INT(H2/16)*16)/24
J2:J29	J2	=INT(H2/16)
K2:K29	K2	=ROUND((H2-INT(H2/16)*16),5)/24
L2:L29	L2	=IF(K2=0,WORKDAY.INTL(D2,J2-1,11)+1,WORKDAY.INTL(D2,J2,11))
M2:M29	M2	=IF(K2=0,0,K2+TIMEVALUE("8:00"))
P2	P2	=D2
P3:P29	P3	=P2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:P30	Expression	=DIASEM(P2)=1	text	NO

 

[Legacy 143009]

Thank you very much. It was quite clear for me but it will be useful for beginners. Thanks for your efforts again