Calculate processing time with fixed time paramaters

**Apel** · 08-28-2009, 05:15 PM

I am wanting to calculate the the processing time for an order that takes place within normal business hours and workweek. A normal day is from 8:00 to 5:00 If a task is started at 2:00 PM Monday and finished at 10:00 AM Tuesday then the result should be 5 hours as I do not want to include any time outside of normal hours. I can figure out how to subtract dates and times but not how to bridge a day(s). My data is somewhat flexible as I have not started the project yet. I can use separate cells for the times and dates or have two cells that use both incorporate the date and time (8/27/09 2:00 PM) for start and end time or any other idea.

**DonkeyOte** · 08-28-2009, 05:28 PM

If start datetime is in A1 (eg 24/08/2009 14:00) and end datetime is in A2 (25/08/2009 10:00) then:

Please Login or Register  to view this content.

or if start/end never on a weekend then

Please Login or Register  to view this content.

note: above utilises NETWORKDAYS function which pre XL2007 requires activation of the Analysis ToolPak Add-In - activated via Tools -> Add-Ins

Above is c/o daddylonglegs

**Apel** · 08-31-2009, 04:54 PM

I copied the information as supplied cell by cell but received a value error. The Analysis Toolpak is installed and the cells are formated as instructed. When I trace it it fails at the first evaluation. I am running XL2000 although I have also tried it on another machine running XL2003. Is there anything I am missing?

**daddylonglegs** · 08-31-2009, 04:59 PM

Works OK for me, can you post the formula you are using?

If you have a (Non-english) European version of Excel then you may need to replace commas , in the formula with semi-colons ;

**Apel** · 09-01-2009, 12:07 AM

I've tried both the posted formulas. I have attached my test file.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**DonkeyOte** · 09-01-2009, 02:02 AM

I just opened the file - in A3 is result of 5:00 which is correct I believe ?

**Apel** · 09-01-2009, 08:54 AM

Something must not be installed right then on my machine. I will go over it again and maybe see if I can find one that has XL2007. Thanks for the solution and for taking a look at my file.