workday formula containing different working hours and breaks during week

[mlan]

Hello Everyone

I'm creating a production plan to calculate tarket date/time for my modules based on the rumtime.
I'm using the formula provided by daddylonglegs in this thread:
excelforum.com/excel-formulas-and-functions/979700-calculating-target-date-time-based-on-start-date-and-hours-excel-2007-a

This works fine if working hours are the same every day, and there are no breaks.

Problem 1
I have a shorter working day on fridays.
I'm trying to use an "if" formula to work around this, but end up with a time difference on fridays. The time difference (03:25) is the same as the difference between "end work" for the different days.
I tried just adding the time difference as you can see in the attached workbook, but that didn't work in every case.

=IF(TEXT(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,"dddd")<>"friday",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,WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-E$2)/(G$2-E$2),1)-1,D$2:D$10)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-E$2,G$2-E$2)+G$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 on monday-thursday
G2 is end work on friday
D2:D10 is holidays

Problem 2
My other problem is worktime breaks - morning break and lunch break
I can easily add both breaks:

=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 are 2 breaks, but when there is only 1 break I get the end time wrong.
I need different combinations to be found automatically:
- One morning break
- One lunch break
- One morning break + one lunch break
- 2 morning breaks (over the weekend)

And in the end I need to combine it all into one formula (hopefully)

I've added a workbook with my work so far, and a manually created timetable for 2 weeks, where you can check for the right time stamps.

Any help on either of my two problems would be appreciated