Excluding hours in Excel

Question

I'm trying to calculate the processing time from time received to time completed, and I need to exclude the hours of 11 pm - 8 am because they're non-working hours.  I tried playing with the NETWORKDAYS function but couldn't figure it out.

Categories are:

• Received
• Processing End (time completed)
• Start time (8:00am)
• End time (11:00pm)

This is what I have tried so far, but it throws error:

=(NETWORKDAYS(Received,Processing_End)-1) *
 (End_time-Start_ti‌​me) +
 IF(NETWORKDAYS(P‌​rocessing_End, Proces‌​sing_End), 
    MEDIAN(MOD‌​(Processing_End,1), E‌​nd_time, Start_time),‌
    ​End_time) - 
 MEDIAN(NET‌​WORKDAYS(Received,Re‌​ceived) * 
 MOD(Received‌​,1), End_time, Start_t‌​ime)

How can I do this?

Answer 1

It would have been helpful if you had provided some sample data.

I assume that time received and time completed are never between 11 pm and 8 am, because the shop is closed, but it’s open seven days a week (365 days a year), so we don’t need to worry about weekends.

One way of looking at the problem is that, if a job is completed at the same time that it is received, but n days (i.e., 24×n hours) later, then a simple B2-A2 subtraction will yield 24×n hours, when you want a result of 15×n hours, because the shop is open 15 hours every day.  So I think the solution is to do the simple subtraction, and then subtract 9 hours for each day the job is in the system.

This formula should do it:

=$B2-$A2 - (INT($B2)-INT($A2))*(1+TIME(8,0,0)-TIME(23,0,0))

where I use INT($B2) and INT($A2) to get the day portions of the date/time values, and the messy-looking expression (1+TIME(8,0,0)-TIME(23,0,0)) is 9 hours (8 am tomorrow minus 11 pm tonight).

Here’s a screenshot: