Calculate time for working hours when start time falls outside of working hours

Hey All,

It's my first time posting in these forums and I would like to first state that I have searched all over for the answer to this, but had no luck finding the answer to my specific question.

I am trying to calculate the time it takes from the moment a PO is received by our office (via email), to the time it is entered by us. I have had luck thus far in calculating for net work days only (Mon-Fri) and our hours of operation (8:30am-5:30pm) but the problem arises when a PO is received after normal working hours. For example, if a PO arrives Saturday at 12:00pm, and it was entered on Monday morning at 9:00am, the calculation should only be that it took 0.5 hours (or 30 mins) of working hours for the PO to be entered.

Instead of 30 mins, I am getting 6 hours (because it is now counting the remaining 5:30 on Saturday, excluding Sunday, and then adding the correct 30 mins on Monday). Do any of you guys know how I would go about fixing this problem?

Attached is an example spreadsheet of what I am trying to work out.

Thanks!
Logbook problem.xlsx

Working with dates and times is always a pain in the neck. Normally, I would recommend a bunch of helper columns to keep all the date and time checking straight, but here it is all at once. In J4:

Please Login or Register  to view this content.

This formula in O4 will give you the required results

=24*((NETWORKDAYS(B4,D4,N$4)-1)*(M$4-L$4)+E4-MEDIAN(C4*NETWORKDAYS(B4,B4,N$4),L$4,M$4))

format as number and copy down

I think mine still works if you read what I provided. My calculation was to go into the J column and it generates a new start time. You can then use it for your calculations. My result is a date and time, so be sure it is formatted as a date and time, otherwise it looks like numbers in the 40,000s.

In your second spreadsheet my formula creates 1/20/2014 8:30am for the highlighted row.

I am thinking DaddyLongLegs method may provide a cleaner solution, but I would have to parse it first.

The formula I suggested handles start times before 08:30 OK.....the problem with that example is that 18/01/2014 was a Saturday so you actually have the "entered" time/date outside working hours, my asumption was that the entered time would always be within working hours. If you leave the times the same but change the date (on both Received and Entered) to 17/01/2014 then you'll get 7.85 as required

If you do need to handle "Entered" times that are outside working hours (but still count only the actual working hours, so 06:23 on 18th Jan 2014 to 16:21 on 18th Jan 2014 will be zero working hours because it starts and ends on the same Saturday) then use this version

=24*((NETWORKDAYS(B4,D4,N$4)-1)*(M$4-L$4)+IF(NETWORKDAYS(D4,D4),MEDIAN(E4,L$4,M$4),M$4)-MEDIAN(C4*NETWORKDAYS(B4,B4,N$4),L$4,M$4))

If you want you can also add another IF function so that you get a blank unless all 4 values are populated, e.g.

=IF(COUNT(B4,C4,D4,E4)=4,24*((NETWORKDAYS(B4,D4,N$4)-1)*(M$4-L$4)+IF(NETWORKDAYS(D4,D4),MEDIAN(E4,L$4,M$4),M$4)-MEDIAN(C4*NETWORKDAYS(B4,B4,N$4),L$4,M$4)),"")