Calculate task completion date over multiple shifts omitting breaks

Hi,

I have a spreadsheet with tasks listed in order. I have a task start date and the number of working hours it takes to complete the task. I have used the below formula to calculate the task completion date, but it is based on a regular 8 to 5 working day and it excludes Saturdays, which are worked here.

=IF((MOD(I2,1)+MOD(H2,8)/24)>2/3+0.0000000001, WORKDAY(I2,INT(H2/8)+1,$N$2:$N$21)+MOD(I2,1)+MOD(H2,8)/24-1/3,WORKDAY(I2,INT(H2/8),$N$2:$N$21)+MOD(I2,1)+MOD(H2,8)/24)

Where I2 is the Start Date and H2 is the hours required for the task and N2:N21 is the holiday list.

We have a 1st shift from 6:00 AM to 2:30 PM and a 3rd shift from 9:30 PM to 6:00 AM. There are 2 designated 15 minute breaks for each shift (1st Shift - 8:00 AM to 8:15 AM and 1:00 PM to 1:15 PM; 3rd Shift - 11:30 PM to 11:45 PM and 4:30 AM to 4:45 AM) and each shift has a 30 minute lunch break (1st Shift - 11:00 AM to 11:30 AM; 3rd Shift - 2:30 AM to 3:00 AM).

I need to be able to calculate the completion date and time of the task while accounting for the breaks and lunch time for each shift as well as the 'dead' period between the shifts. Can anyone help?

Re: Calculate task completion date over multiple shifts omitting breaks

Hi Herbds7,

Thank you for your response.... I looked at the sheet you sent me and I saw the defined names, but I didn't see any formulas.... I'm okay with understanding complex formulas, it's just a matter of writing them myself that gives me some trouble....