How to calculate completion date/time ignoring nonworking hours

[sosession]

Known Variables

• A1 = start date and time (6/16/15 6:00AM)
• B1 = estimated hours to complete work order (15)
• Work schedule is 6AM to 6PM, weekdays only

What formula can I use to calculate the end date/time, ignoring nonworking hours? It currently says that the estimated completion will be 6/16/15 9:00PM. However, that is outside of working hours. The correct estimated completion date/time should be 6/17/15 9:00AM. How do I ignore nonworking hours and weekends? Please help!

 

[sosession]

So far so good. Even though Barry put it together, thank you very much for getting it to me. Credit to you as well! One last thing, which part of the formula would/could I add holidays to exclude from the work schedule? Thanks in advance!

 

[James006]

With a range named Holidays .... =WORKDAY(A1,CEILING(((A2/24)+MOD(A1,1)-B1)/(B2-B1),1)-1,Holidays)+MOD(A1,1)+(A2/24)-CEILING(MOD(A1,1)+(A2/24)-B1,B2-B1)+B2-B1

 

[kenwdav]

Sorry it has been a while since this post but I have a very similar spreadsheet I am working with and cannot get this to work either but cannot download the file with the link. Can you send me the file please so I can see what I am doing wrong.

Thanks

 

[James006]

Hi Ken,

Please refer to the pm ... I just sent you ...

Cheers