Calculate Networkdays for business hours only

Good day,

I am attempting to create an excel formula that will not count hours:minutes after 17:00. If the time goes over 17:00, I would like it to start on the next business day.

Working hours: 07:00-17:00

Current formula: =10*(NETWORKDAYS(D2,E2)-1)-24*((MOD(D2,1)-MOD(E2,1)))

Format for time entered is m/d/yyyy h:mm

This works great, until it comes to work that was performed after working hours.

example: (task started)cell D2: 1/10/13 18:55 (task ended)cell E2: 1/11/13 08:13 ...End result: -0.70 hours. I'm looking for 1.13 hours

Please help,


Thank you. JD

Kevin,

Thank you for responding. I used your formula and saw that it was still off. I may have left something out. I'll paste a sample of what i have.

The only time that will be after hours will be the start time. the end time will always be within business hours.

Thank you very much. That seems to do the trick. I appreciate it.

JD

Thanks guys, even 3 years after your posts it helped me a lot :-) Good work! Tom

Extremely helpful. Was able to validate the negative values as time occurring during non-business hours.

Coming back to this formula, I'm having issues. My business hours are 8:00AM - 5:00PM.

(start time) D2 = 2/18/2016 8:00:00 AM
(end time) E2 = 2/18/2016 6:00:00 PM

Business Hours = 9

Formula is returning 10 business hours, =24*((NETWORKDAYS(D2,E2)-1)*("17:00"-"08:00")+MOD(E2,1)-MEDIAN(NETWORKDAYS(D2,D2)*MOD(D2,1),"17:00","08:00"))

What am I doing wrong?