Excluding hours for processing time

HannahNeedsHelp

New Member
Joined
Jul 7, 2017
Messages
15
Hello,

Trying to exclude non-working hours from the calculation of processing time by employees. Keep getting Name errors using this formula:
=Networkdays (Received, Processing End)-1)*(Stop time-Start time)+IF (NETWORKDAYS(Processing End, Processing End), MEDIAN(MOD (Processing End,1),Stop time, Start time),Stop time)-MEDIAN (NETWORKDAYS (Received, Received)*MOD (Received,1),Stop time,Start time)

Categories are:
Received (time we get the request)
Processing End (time it's completed)
Start time 8:00
End time 11:00 pm
(Want to exclude 11pm-8am from calculation as these are non working hours.

Thanks!
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
How are you entering in your Received and Processing End times/dates? Are there two separate cells: 1 for the time and 1 for date?
 

HannahNeedsHelp

New Member
Joined
Jul 7, 2017
Messages
15
Date and time are in the same cell, ie:

Received 05/30/2017 15:28:00

Processing End 05/30/2017 18:39:00
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,443
Try naming your cells like Processing_End and using this formula:

=(NETWORKDAYS(Received,Processing_End)-1)*(End_time-Start_time)+IF(NETWORKDAYS(Processing_End,Processing_End),MEDIAN(MOD(Processing_End,1),End_time,Start_time),End_time)-MEDIAN(NETWORKDAYS(Received,Received)*MOD(Received,1),End_time,Start_time)
 

HannahNeedsHelp

New Member
Joined
Jul 7, 2017
Messages
15
Got rid of the name error by renaming cells as suggested, but now getting an error that there's a problem with the formula. Am I missing parenthesis somewhere?

Thanks so much!
 

HannahNeedsHelp

New Member
Joined
Jul 7, 2017
Messages
15
This is how I'm entering it, and it's sayin missing opening or closing parenthesis.

=(NETWORKDAYS(Received,Processing_End)-1)*(End_time-Start_time)+IF(NETWORKDAYS(Processing_End,Processing_End),MEDIAN(MOD(Processing_End,1),End_time,Start_time),End_time-MEDIAN(NETWORKDAYS(Received,Received)*MOD(Received,1),End_time,Start_time)
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,443
There is a missing closing parenthesis just before the last -MEDIAN(...
Just copy-and-paste the formula from Post #4.
 

HannahNeedsHelp

New Member
Joined
Jul 7, 2017
Messages
15
Thanks. I cut and pasted directly from the original formula, and it's back to giving me name errors. :(
 

HannahNeedsHelp

New Member
Joined
Jul 7, 2017
Messages
15
Got rid of the name error by refercing the cells, rather than names. But the formula isn't factoring in those processong times that are over 24 hrs.

Ex:
Received 19:55:00 on 5/31
Processed 22:41:00 on 6/1.
Formula shows it took 1:46:00, but it should be 16:46:00.

Advice?
 

Forum statistics

Threads
1,078,240
Messages
5,339,038
Members
399,275
Latest member
amrita17170909

Some videos you may like

This Week's Hot Topics

Top