Working Hours Taken to Complete A Job

[jammy1812]

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!! =)

[Pepe Le Mokko]

Does the attached help?WorkHours.xlsx

[jammy1812]

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?

[daddylonglegs]

Hello jammy1812,

The formula you quoted works when the start and end dates/times are within the working hours.....so it doesn't work with your example because 17:33 is clearly outside the 08:00 - 16:30 working day. Try this revised formula which will cope with any start/end times/dates

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

format result cell as [h]:mm

[jammy1812]

Thanks DLL, works perfectly!

COYS! =)