Thread: Working Hours Taken to Complete A Job

Hey guys,

I have a formula to work out working hours to complete when a order was received to when it was validated.

The current formula is:

"=IF(COUNT(J71,L71)=2,(NETWORKDAYS(J71,L71,'2012 details'!B$6:B$14)-1)*("16:30"-"8:00")+MOD(L71,1)-MOD(J71,1),"")"

'2012 details' specify our bank holidays which are:
Public Holidays 2012
02/01/12
06/04/12
09/04/12
07/05/12
04/06/12
05/06/12
27/08/12
25/12/12
26/12/12


Column J = Order Received
Column L = Order Validated

However, the times aren't really calculating properly, as results I'm getting are not correct.

i.e.

J = 02/02/12 17:33
L = 03/02/12 09:13
Output = 0:10

Working hours are: 8:00 - 16:30

Output format is: [h]:mm


Can anyone help with this please?
Or does anyone have a better method? Cheers!! =)

Hey,

Thanks for the response.

I'm having some issues when I'm trying to drag this down to the remainder of the sheet (have 700+ entries a year).

Basically, the working times and public holidays in the formula also changes i.e. If row 1:10, the next row down will select row 2:11.

Any thoughts?

Thanks DLL, works perfectly!

COYS! =)