Using Networkdays with the Now() function to work out lapsed time since job was logged.

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.

Networkdays normally compares different dates, but in two places, NETWORKDAYS(NOW(),NOW()) and NETWORKDAYS(BQ2,BQ2), you have the same dates. This will always be 1 and evaluate to True, so what is the point?

I don't think you should use NOW() either as it contains the date part, TODAY() is better.

And the formula returns a time, so your last bit is pointless, just format it as [h]:mm.

In all, I think your formula should be

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

If you are trying to include a start time (in BQ2), and the finish date and time, it would be better to record that in a cell and use that cell in your formula instead of NOW()(, as NOW() will keep moving on, so this formula will be updating every second.

I've been playing with it, and this works for me, assuming the finish time is in BR2, and formatted as [h]:mm as I mentioned

=MAX(0,NETWORKDAYS(BQ2,BR2)*("16:30"-"08:00")-(MEDIAN(MOD(BQ2,1),"16:30","08:00")-"8:00")-("16:30"-MEDIAN(MOD(BR2,1),"16:30","08:00")))

What I changed it to is to calculate a full days effort for each date, then subtracted the poart not worked at the beginning of the start day, and then subtracted the part not worked at the end of the finish day. The MAX(0, part is just a simple way to avoid an error if the finish was before the start.

Hi bob, thanks for your help with this...I am going to use this formulae to update time lapsed from start date to now(), so I want the formulae to update how many hrs:mins it's been since the start date to now (or refresh), will your mods still work ?

I'll try on Monday when back in the office...thanks for your help bob, most appreciated.

Mark.

Originally Posted by daddylonglegs

It won't always be 1, Bob. If BQ2 is at the weekend then NETWORKDAYS(BQ2,BQ2) = 0

The formula is designed to calculate elapsed work hours between BQ2 and NOW(), even if one or both of those is outside working hours, e.g. at the weekend or evening. Using NETWORKDAYS to check whether those dates are at the weekend or not also allows you to easily modify the formula to exclude listed holiday dates if required

Good point sir. I remember seeing that in one of your formulae last year, which I assumed the OP had got it from, I just thought you had made a typo. Should have known better

Hi, it maybe one of yours, saves a bit if time borrowing formulae and then trying to adapt them....only if it works though, never too old to learn !!! Will give it ago and let you know if it works as I intended. Thanks mark.

that worked fine, its now showing the correct data, cheers for your help with this.