Calculate expected time to finish task

Hey all!

I am trying to create a file that will provide the expected time to finish a task that takes X hours to complete and with predetermined working hours.

For example, if a task takes 6 hours to complete, my team starts work on it at 12:00 on 3/1/2017 and my team's working hours are 9:00-16:00, the expected time to finish should be 11:00 on 3/2/2017 (4 hours until the end of day plus 2 hours from the start of the next working day).
Tasks can be received at all times around the clock, not only during working hours (if shift starts at 9 and task is received at 7, work on it will start at 9).

The file that i'm creating should be able to address weekends (in the above example if 3/2/2017 is a Saturday that the expected time to finish would be 11:00 on the following Monday).
Also it will be used by several teams, some of them work night shifts so the solution has to be suitable for those shifts as well (for example start working at 23:00 and finish at 7:00- there might be a problem with "Shift end" values that are smaller than "Shift start").

As 2 people in my company already tried and failed, I must turn to the experts for help
What do you think??

Thanks,
Oran

http://www.excelforum.com/excel-gene...ml#post4576825

Hi @tim201110 I spent a couple of hours trying to understand what exactly you did in the referred thread but unfortunately I couldn't

If it's not too much trouble for you, could you please explain how to create a file that can receive as input:
1) Team working hours, and
2) Task duration time
and provide the Task end date & time as output.

The file will be used by several teams that work different shifts, including shifts that start before midnight and end after midnight (22:00-6:00 for example). Unlike the case in the referred thread, I don't need to consider breaks, but I do need to consider weekends and allow the option of a task being received out of working hours- for example if my team works 9-18 and we have a task that takes 1 hour received at 7AM, the Task end time should be 10AM and not 8AM.

Thanks very much for looking into it!

Brilliant Piet Bom! From first glance it looks exactly like what I need. Thank you so much for sharing!

hey piet thanks for this file i have a similar problem . and absolutely love this resolution the only problem i am facing is i want it to consider Friday,Saturday and Sunday a weekend instead of just Saturday and Sunday

theres a missing bracket just before the first "*MOD", here is the corrected formula for testing in L11:

Please Login or Register  to view this content.