Calculate task completion date/time while accounting for working shifts

The following thread gets me very close to solving a problem I have struggled with for many months.

http://www.excelforum.com/excel-form...-relevant.html


The example in the thread finds the completion date/time (C1) given a start date/time (A1), a duration (B1) and a start of day time (D2) and end of day time (E2).

A1=6/11/2012 16:33
B1=1:00
C1=(NETWORKDAYS(H2,K2,_NWD)-1)*(_end_of_day-_start_of_day)+IF(NETWORKDAYS(K2,K2,_NWD),MEDIAN(_start_of_day,_end_of_day,MOD(K2,1)),_end_of_day)-MEDIAN(NETWORKDAYS(H2,H2,_NWD)*MOD(H2,1),_start_of_day,_end_of_day)
D2=9:00
E2=17:30


My problem is that our shifts run from one day into another through midnight. We work two 8 hours shifts per day 5 days per week beginning on Sunday at 10:00 pm ending on Friday at 2:00 pm. This requires different start_of_day and end_of_day times for Sunday,Mon-Thursday, and Friday.

Does anyone have any experience with this?

I apologize in advance. I am new to the forum. If my post needs more details please just direct me.

Calculate Task Completion Time.xlsx

Attached is a simple example of what I need. I have a series of jobs with a customer requested due date. I can calculate the job completion time and dates given a single default daily work period. Unfortunately, my work periods span over midnight so I don't know how to represent the hours worked in an single default start and stop time. If the solution could be more generic to look at the work schedule for each given day then I could make adjustments through the week in an effort to meet customer schedule when it is apparent that we are falling behind.

I represented the work schedule in a table to the right with a "1" in every hour that is scheduled to be worked.

Ok, I didn't get any response to my original request so I spent some time to try to make my spreadsheet easier to understand.

Completion Date-Time Calculator.xlsx

Restatement of problem:

I have a list of jobs for which I need to calculate a completion time and date. The second job in the series cannot begin until the first job is complete and so on. The finish time of each job must consider weekends, holidays and the working hours of each day. I found a solution in other threads that works given a default start and stop time that remains the same every day. I'm looking for a solution that allows me to change the working hours by date or at the very least add a shift of overtime hours on a Saturday.

Start Time (A1): User entry of the start time and date of the first job

Duration (B1:B20): User entry of the duration of each job in minutes

Finish Time (C1:C20): Calculation of the completion time using a formula (see below) I found in several other similar threads.

=WORKDAY(A2-1,CEILING(((B2/1440)+MEDIAN(NETWORKDAYS(A2,A2,Holidays)*MOD(A2,1),DayStart,DayFinish)-DayStart)/(DayFinish-DayStart),1),Holidays)+MEDIAN(NETWORKDAYS(A2,A2,Holidays)*MOD(A2,1),DayStart,DayFinish)+(B2/1440)-CEILING(MEDIAN(NETWORKDAYS(A2,A2,Holidays)*MOD(A2,1),DayStart,DayFinish)+(B2/1440)-DayStart,DayFinish-DayStart)+DayFinish-DayStart

DayStart (F$4): Named Range for default start time used in above formula

DayFinish (G$4): Named Range for default finish time used in above formula

Holidays (G$14:G$23): Named Range for holidays to be excluded

Any help or direction would be appreciated.