Network Hours

[Mikeymike_W]

Hi Everyone,

I have been trying to get this sorted for a while but no luck.

I have a spreadsheet (obviously), i have several hidden columns which automatically capture the time and date that certain cells are populated.
I want to subtract the difference between two of these cells as it will let me work out certain KPI's.
I need this time to be in hours (or minutes).
I have tried out some formulas but the hours are part of the 24hour day rather than the actual working day i.e. if the entry is 25/04/2016 16:00 & 26/04/2016 09:00 then the result will be 17hours.

I had previously posted this question and kindly received some support from "RCBricker" who provided me with the following:

=SUM(IF(OR(TIME(17,0,0) < TIME(8,30,0),$B$5 < $B$4),0,
(NETWORKDAYS($B$4,$B$5)
-(NETWORKDAYS($B$4,$B$4)
*IF(MOD($B$4,1) > TIME(17,0,0),1,
(MAX(TIME(8,30,0),MOD($B$4,1))-TIME(8,30,0))
/(TIME(17,0,0)-TIME(8,30,0))))
-(NETWORKDAYS($B$5,$B$5)
*IF(MOD($B$5,1) < TIME(8,30,0),1,
(TIME(17,0,0)-MIN(TIME(17,0,0),MOD($B$5,1)))
/(TIME(17,0,0)-TIME(8,30,0)))))
*(TIME(17,0,0)-TIME(8,30,0))*24)-SUM(SUM(INT((WEEKDAY($B$4-2)-$B$4+$B5)/6)-1)*4))

This formula worked for 50% of the cases.

I was then provided with the following:

=SUM(IF(OR(TIME(17,0,0) (NETWORKDAYS($E$4,$E$5)
-(NETWORKDAYS($E$4,$E$4)
*IF(MOD($E$4,1)>TIME(17,0,0),1,
(MAX(TIME(8,30,0),MOD($E$4,1))-TIME(8,30,0))
/(TIME(17,0,0)-TIME(8,30,0))))
-(NETWORKDAYS($E$5,$E$5)
*IF(MOD($E$5,1) (TIME(17,0,0)-MIN(TIME(17,0,0),MOD($E$5,1)))
/(TIME(17,0,0)-TIME(8,30,0)))))
*(TIME(17,0,0)-TIME(8,30,0))*24)-IF(OR(TEXT(E4,"dddd")="friday",TEXT(E5,"dddd")="Friday"),SUM(SUM(INT((WEEKDAY($E$4-2)-$E$4+$E5)/6))),SUM(SUM(INT((WEEKDAY($E$4-2)-$E$4+$E5)/6)-1))))

However I couldn't get this working as there is an error in the formula somewhere.

SO my question is can anyone see the issue with this formula or can anyone provide me with a solution to my initial query?

Many thanks in advance,

MIke

 

[Chrisdontm]

I am not sure if I am following you all the way, but per your example, just using times,
=MOD(B5-B4,1)*24 would give you the 17-hours. ( Of course you would want it formatted as a number )

 

[Mikeymike_W]

Hi Chrisdontm,

Thanks very much for your response.

I need the formula to take into account network days so out of hours is not included in the result (preferably 08:30-17:00 Mon-Thu, 08:30-13:00 Fri working schedule).

Many thanks,

MIke