Calculate End Date & Time Based on User Input

Hi,

In order to assist with machining throughput, the machine shop manager has asked for a spreadsheet to calculate the finish date and time of the set up and machining operations. The idea is that he will input the start date & time, operation number etc into the first tab, then the second will populate after running a macro.

I have searched the forum (and Google) and found an old post answered by Daddylonglegs which will calculate the end date/time for working days only as follows:

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

Where A2 is the start date and time, B2 is the length of the operation, E2 is the shift start time and F2 is the shift end time.

The issue I have with this as we have shift patterns covering Saturday and Sunday too. Is there any way of actually calculating the end date based the shift patterns (Monday - Friday 06:00 - 21:00 and then Saturday and Sunday 06:00 - 12:00 (note that there is also some down time every day)).

Any help greatly appreciated!