Automatically identifying breaks, date change and holidays in production plan

mlan1604

New Member
Joined
Jun 4, 2014
Messages
7
Hello everyone.
I am creating a production plan, where I need to automatically identify breaks, date changes and holidays, to omit these from the production cycle time.

Imagine that I have to produce 11 modules in 10 working days. With a 37 hour working week this gives a cycle time of 403 min pr. module.
I have manually created a 2 week timetable, where I have simply added the breaks and date changes to the time intervals.
Skærmbillede 2021-02-19 104617.png

But I need this 2 week cycle to continue for 5 month, and want to automate the process.

I'm thinking the process would be something like this:
enter cycle time in cell 1 ( maybe a different cycle time, for each module, depending on it's complexity)
enter start date and time in cell 2
sum cell 2 with cell 1 to get end time without breaks
Identifying date, weekday number and time of day
Use formula or VBA to look at my holiday table and working week table to identify breaks, day changes, weekends and holidays, between end time and start time.
adding these to create new end time.
repeating the process for the next end time.

The process of identifying breaks etc. needs to happen several times, as you may pass a new break when you add the first break to the end time.
Skærmbillede 2021-02-19 111141.png
Skærmbillede 2021-02-19 111356.png


I don't know which way to go in order to automate the process.
I've been looking at the new "xlookup" formula i Excel365, but am thinking VBA and filling arrays is a better way to go.
What do you think? Please help me.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mlan1604

New Member
Joined
Jun 4, 2014
Messages
7
Now I've come a litle further myself.
I've found a worksheet formula on another forum, which takes care of non-working hours, weekends and holidays.

The formula I found is:
=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

Calculating Target Date/Time Based on Start date and hours - Excel 2007 [SOLVED]

Now I only need to incorporate the two breaks and different work time for friday.
Can anyone help me doing that?
 

mlan1604

New Member
Joined
Jun 4, 2014
Messages
7
I started out with a massive task, but have now boiled it down to a small problem.
Now I've also managed to take both brakes into account, but only if both breaks exsist.
Newest version of my formula is:

=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+MAX(0,(MIN($F$2,$F$3)-MAX($E$2,$E$3)))+MAX(0,(MIN($F$2,$F$4)-MAX($E$2,$E$4)))
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
E3 is start 1st break
F3 is end 1st break
E4 is start 2nd break
F4 is end 2nd break
D2:D10 is holidays

This works well when there is 2 breaks, but when there is only 1 break I get the end time wrong.
I can see, that both breaks are added every time, but there need to be different combinations:
- One morning break
- One lunch break
- One morning break + one lunch break
- 2 morning breaks (over the weekend)

How do I change my formula to only add the correct break(s)?

Also I still haven't figured out how to incorporate a different work time for fridays?

Please help
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top