Working out Networkdays in hours

[kylerisi]

Hi Team,

I currently have a formula which works out the number of hours passed between a particular date and Now, i really need this to be worked out in business hours much like =networkdays, can anyone please provide me with some help on how to achieve this.

below is the formula i am currently using

=if(O6="","",(if($Q6="C","",(((NOW()-O6)*24)))))

currently my formula checks 06 for "C" if true it stays blank, if False, it then works out how many hours have passed between a cell in another sheet and the current time.

ideally if i could work out the number of ours between 9-6 monday -friday, that would be super amazing.

I have browsed other posts on this forum, but I am confused on how to embed the NOW() part into my formula

any help would be greatly appreciated.

Best Regards

Kyle

 

[gaz_chops]

I think the fact that you are using now() will be an issue, as today is a Sunday it won't be a business day, but the formula will add the difference between now() & "17:00"!

 

[kylerisi]

Hi Gaz_Chops here is the formula i entered

=if(or(O6="",$Q6="C"),"",((NETWORKDAYS(O6,NOW())-2)/24*9)+(MOD(NOW(),1)-"09:00")+("18:00"-MOD(O6,1)))

I have amended the formula are stated - the expected result should be 4:53 minutes based based on the time and date in O6 which is 30/10/2015 15:00:00 but the result being returned is 19:50

best regards

kyle

 

[gaz_chops]

Maybe it is google, i get 5:05 running it now.

[TABLE="width: 278"]
<colgroup><col><col span="13"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/10/15 15:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Realised i put the wrong date in! If I use 30/10/15 I get a minus, this is because networkdays won't count the weekends!

Try

=IF(OR(O6="",$Q6="C"),"",((INT(NOW())-INT(O6)-2)/24*9)+(MOD(NOW(),1)-"09:00")+("18:00"-MOD(O6,1)))

[/FONT][TABLE="width: 278"]
<colgroup><col><col span="13"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5:21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/10/15 15:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Lucida Grande]

 

[kylerisi]

That result is wrong, you have used the 29th of October which should return 14:05 not 5:05. 5:05 is the expected result if the date was 30/10/2015.

 

[gaz_chops]

See my last post.

 

[kylerisi]

Hi Gaz,

Thank you for your help, However the result should say 3:00 not 5:00 since saturday and sunday should not be counted by the formula. this result seems to suggest that it is counting either saturday or sunday.

 

[kylerisi]

Gaz_Chops

I found a formula which i was able to adjust which seems to work

=IF(OR($O8="",$Q8="C"),"",(NETWORKDAYS($O8,$A$12)-1)*("18:00"-"9:00")+IF(NETWORKDAYS($A$12,$A$12),MEDIAN(MOD($A$12,1),"18:00","9:00"),"18:00")-MEDIAN(NETWORKDAYS($O8,$O8)*MOD($O8,1),"18:00","9:00"))


the only thing is that i need to tell the sheet what the current time is in cell A12 using =Now() rather than it being embedded into the formula, however this is only a small compromise,

thank you for your help.