Variable working hours

[Lou745]

Hi all,

I'm looking at creating a page that can help me with job planning. Essentially I want to give the system a start date and duration (cycle time) then based on this it tells me when it will be finished. However this is part is we run 24/5 and the Friday being a different amount of working time.

Is there a way to tell excel at 7pm the working day has finished and it must then not anticipate work to be complete again until Monday at 7:00 am

So it slips the weekend and does not try to consume time that cannot be consumed.

Hopefully you can help me

Best regards

Louis

 

[bebo021999]

Could you upload a XL2BB mini sheet?

 

[Lou745]

Copy of Machine Plan 01.11.2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	HPMU01 - Updated 01.11.2021
2	Production Order/ Material Status	Part Number	Description	Plnt	Quantity	Delivery Date	Start	Shift Allocated	Shift time allowance	Production Hours	Productive Days	Finish	Actual Finish	Comments
3	4817853	289014668	QUAD LID DETAIL DOWEL ASSEMBLY	EI10	10	29.10.2021	21.10.2021 PM	SS	6.5	109	16.8	25.10.2021 AM	25.10.2021 AM
4	4818385	289008834	LID TQ-S DOWEL ASSY	EI10	10	29.10.2021	25.10.2021 AM	SS	6.5	86	13.2	25.10.2021 PM	25.10.2021 PM	Side Hole Only
5	4819638	289008834	LID TQ-S DOWEL ASSY	EI10	10	29.10.2021	25.10.2021 PM	SS	6.5	66	10.2	26.10.2021 AM	26.10.2021 AM	Side Hole Only
6	4820400	405021869	BASE - MSC CONCEPT 3 -MK2	UK50	10	19.11.2021	26.10.2021 AM	SS	6.5	34	5.2	27.10.2021 AM	27.10.2021 AM
7	4819622	289010874	LCTOF MS1 HOUSING DOWELLED ASSY	EI10	10	12.11.2021	27.10.2021 AM	SS	6.5	20	3.1	01.11.2021 AM
8
9	Time Time Allocation Daily					Start Times				Finish Times
10		Monday	Tue-Thur	Friday	Total		Monday	Tue-Thur	Friday		Monday	Tue-Thur	Friday
11	SS	6.5			32.5	SS	6:00 AM			SS	14:00:00 or 21:00
12	DS	12		10	58	DS	6:00 AM		06:00:00	DS	21:00		19:45
13	TS	15	20	17	92	TS	06:00:00	00:00	00:00	TS	00:00	00:00	19:45
14
15		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
16		1	2	3	4	5	6	7
17	SS	0	6.5	6.5	6.5	6.5	6.5	0
18	DS	0	12	12	12	12	10	0
19	TS	0	15	20	20	20	17	0
HPMU01

Cell Formulas
Range	Formula
I3:I7	I3	=IF(H3="SS","6.5", IF(H3="DS","58",IF(H3="TS","92",)))
K3:K7	K3	=J3/I3
E11	E11	=B11*5
E12	E12	=(B12*4)+D12
E13	E13	=(C13*3)+B13+D13

Cells with Data Validation
Cell	Allow	Criteria
H3:H7	List	SS,DS,TS

 

[Lou745]

Hi,

Essentially I want to align available productive hours (Shift patterns available hours) with productive time (how long to make the product) and then give me a finished date based on the data entered. but it must take into account shift patterned applied (Single shift, Double shift, triple shift) each of the shift patterns has varied start times and thus would provide more or less available working hours. but there are times when we will want to apply the triple shift with the work starting on a Friday, but it must take into account the only available hours allowed on a Friday ends at 19:45 and that's the maximum amount of hours that can be applied.

Hopefully that makes sense