Help: production scheduling, network days, estimated job finish time formula

Hi all , am stuck with a problem to create a new spreadsheet for production planning :

objective:
to calculate the job finish time to produce target amount of units on a machine which produces 1200/1500/2300 etc units/min.
variable:
network days are MON-FRIDAY , 09:00 to 17:00 with 30 mins break. so the run time is limited to work hours ( 7 hours and 30 mins ), and estimated time finish will be between mon-fri, 09:00 to 17:00. the next job for that machine will then add the previous estimated job finish + changeover time , this will then become the next jobs start time .

An example would be like this :

Batch PRODUCT MACHINE Order Qty Line speed/min Run Rate/min Changeover time Start Time Est finishtime
`O420280 ABC V1 NJP1500 100,000 1500 1200 00:00 01/02 10:30 01/02 11:53
`0420167 ABC V2 NJP1500 473,000 1500 1200 01:00 01/02 12:53 02/02 11:19
0420281 XYZ V1 AF300 42,000 1500 1200 00:00 01/02 10:30 01/02 11:05
0420282 XYZ V2 AF300 216,000 1500 1200 07:00 02/02 10:05 02/02 13:05


i am certain i have used a similar file before , but unable to figure a method to get this working . we have 24 different machines, and multiple tooling changeovers/sku's which is making the current plan really confusing on shop floor.

any help is greatly appreciated. as my excel skills have gone really rough these days.

look forward to any guidance please.

Hi,
Posts without sample files will gain less responses (if any) for the obvious reasons...
Look at the yellow banner at the top of the page to see how to upload your sample file.

Many thanks belinda, file attached.

so essentially to calculate the finish time say for example 11 hours 35 minutes from start time but excluding non working hours.

Hello

I'm working on a similar problem and has found this fomula to get me some of the way. Maybe it is helpful to you as well.

=WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-E$2)/(F$2-E$2),1)-1,D$2:D$10)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-E$2,F$2-E$2)+F$2-E$2)

where
A2 is start date and time
B2 is total hours to complete the task (format: integer)
E2 is start work
F2 is end work
D2:D10 is holidays

It doesn't take your lunchbreak into account, but if your breaktime is fixed and not limited to a specific time of day, then just add 30 min at the end of the formula.
Same goes for the changeover time.

If you figure out how to have different working hours on fridays please let me know.