Hi, I'm trying to use the networkdays function to count the hrs/mins lapsed from a start point till now, but only allowing work hrs/mins to be counted, the formulae sort of works but does not give me the correct hrs and minutes. I need to calculate from the date/time the jobs was logged till now how much work time has elapsed so I can show this on a dashboard so we can see how long before 0ur SLA runs over.


=(NETWORKDAYS(BQ2,NOW()-1)*("16:30"-"08:00")+IF(NETWORKDAYS(NOW(),NOW()),MEDIAN(MOD(NOW(),1),"16:30","08:00"),"16:30")-MEDIAN(NETWORKDAYS(BQ2,BQ2)*MOD(BQ2,1),"16:30","08:00"))

I use this to convert to hrs/mins

=INT(BS2*3)&" days "&FIXED(MOD(BS2*24,8.5),2)&" Hours"

I think I have an issue because its subtracting one of now() which is showing a negative number, for start dates/times of today ???

Does anybody have any other ways of working out lapsed time till now(), so that we can count how many minutes/hours have passed since a job was logged but only using work hrs.

I am using 8:00 - 16:30 as my work hrs not including weekends.

Cheers
mark.