Automatically identifying breaks, date change and holidays in production plan

[mlan1604]

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.

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.

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.

 

[mlan1604]

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]

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