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.

This is what ive tried:

=IF(OR([@[Pre Request Date & Time when submitted]]="",[@[Pre Request Date & Time when entered in FIP]]=""),"",NETWORKDAYS([@[Pre Request Date & Time when submitted]],[@[Pre Request Date & Time when entered in FIP]])-1-MOD([@[Pre Request Date & Time when submitted]],1)+MOD([@[Pre Request Date & Time when entered in FIP]],1))

Ideally i would also like to specify what is regarded as working hours (Mon-Thu 08:30 - 17:00, Friday 08:30 - 13:00)

This is really doing my noggin in and i would greatly appreciate any and all help.

Thanks in advance,

Mike

 

[RCBricker]

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.

This is what ive tried:

=IF(OR([@[Pre Request Date & Time when submitted]]="",[@[Pre Request Date & Time when entered in FIP]]=""),"",NETWORKDAYS([@[Pre Request Date & Time when submitted]],[@[Pre Request Date & Time when entered in FIP]])-1-MOD([@[Pre Request Date & Time when submitted]],1)+MOD([@[Pre Request Date & Time when entered in FIP]],1))

Ideally i would also like to specify what is regarded as working hours (Mon-Thu 08:30 - 17:00, Friday 08:30 - 13:00)

This is really doing my noggin in and i would greatly appreciate any and all help.

Thanks in advance,

Mike

try this...

B4 is my start date
B5 is my end date

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

 

[Mikeymike_W]

Hi RCBricker,

Thanks very much for your reply.

I tried your code but there may be a wee problem with it. i get an error stating "there is a problem with the formula, try clicking insert function on the formula tab...."
I think there may be a missing comma or something but no idea where.

When the code is in the formula bar all of the cell references are highlighted blue or red with the exception of the very last line of code:

*(TIME(17,0,0)-TIME(8,30,0))*24)-SUM(SUM(INT((WEEKDAY($B$4-2)-$B$4+$B5)/6)-1)*4))

So maybe the issue is here?

Thanks very much for your help,

Mike

 

[RCBricker]

hi rcbricker,

thanks very much for your reply.

I tried your code but there may be a wee problem with it. I get an error stating "there is a problem with the formula, try clicking insert function on the formula tab...."
i think there may be a missing comma or something but no idea where.

When the code is in the formula bar all of the cell references are highlighted blue or red with the exception of the very last line of code:

*(time(17,0,0)-time(8,30,0))*24)-sum(sum(int((weekday($b$4-2)-$b$4+$b5)/6)-1)*4))

so maybe the issue is here?

Thanks very much for your help,

mike

For some reason, copying off the message is causing an error.

lets try wrapped in code tags

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

nope that failed try this

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

 

[Mikeymike_W]

Hi Again RCBricker,

Its definitely a lot closer than ive been yet but it only appears to be working some of the time and at others just throwing out completely wrong values.

So the format of the date and time in the cells are 24/03/2016 12:30.

So working example:

Start time = 21/03/2016 08:30
Finish Time = 21/03/2016 09:35
Result = 1.08

incorrect result example:

Start Time = 08/04/2016 10:49
Finish Time = 08/04/2016 10:51
Result = 4.04
Real result should be approx. 0.03

I checked out some other results where the two times are very close but the result is correct, its probably a 50:50 split in right vs wrong.

Thanks again for your continued support

Mike

 

[RCBricker]

Hi Again RCBricker,

Its definitely a lot closer than ive been yet but it only appears to be working some of the time and at others just throwing out completely wrong values.

So the format of the date and time in the cells are 24/03/2016 12:30.

So working example:

Start time = 21/03/2016 08:30
Finish Time = 21/03/2016 09:35
Result = 1.08

incorrect result example:

Start Time = 08/04/2016 10:49
Finish Time = 08/04/2016 10:51
Result = 4.04
Real result should be approx. 0.03

I checked out some other results where the two times are very close but the result is correct, its probably a 50:50 split in right vs wrong.

Thanks again for your continued support

Mike

test this one. I get the correct results from your examples.

=SUM(IF(OR(TIME(17,0,0)<TIME(8,30,0),$E$5<$E$4),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(8,30,0),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))))

 

[Mikeymike_W]

Hi RCBricker,

Sorry for the slow reply, ive been away from my computer for a wee while due to work.

I have tried the updated formula but with no success.
I get an error stating there is a mistake in the formula. When looking at the formula in the formula bar the cell references in the second half of the formula are not highlighted.

Thanks for your support,

Mike