# 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

Excel 2010, Tables, Defined Names
Here is just the first step.
It calculates the hours between two date/times.
If the formulas don't scare you away
and you can tweak the Defined Names as indicated,
then I will give you the macro that gives the completion date
with a given amount of time.
http://58918aa17d4002474361-7a…ackcdn.com/05_14_13b.xlsx
If you get *.zip, don't unzip, just rename *.xlsx

• 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....

• Re: Calculate task completion date over multiple shifts omitting breaks

If you can tolerate using a macro to populate the result, you can use the attached file.

## Files

• Re: Calculate task completion date over multiple shifts omitting breaks

In the file I sent you, does the column in
Formulas > Name Manager > Refers To
not show any formulas?
Does the 3rd shift extend into Sunday/Holidays?

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!