NETWORKDAYS - Something a little unusual!!

I'll try and explain in as sane a way as I can. If it sounds insane, then I must also be insane!!!

I have attached a PDF screenshot and will note each formula below.

Here I go...

I work in an office where procedure is that any telephone callbacks required for customers have to be done within 3 hours of the customers initial contact to us. So, when the customer phones in requesting their query to be actioned, the clock starts ticking. In an ideal world, the callback request would be directed to the correct department immediately and possibly dealt with there and then. However, ideal world this is not. So, after 4-5 departments later, it finally arrives at the correct department and, lo and behold, there is less than 3 hours to action the callback. When this event happens, we need to keep a late arrivals log, so when the proverbial does hit the fan, at least our department can say we received it late and here's the evidence. In some instances (and I refer to the middle row of the 3 examples below) there maybe a callback target time of 17:00; however, our department didn't receive the callback request until the following day at 09:00. No matter how quickly we could try to answer this particular callback, the proverbial has already hit the fan. Again, as long as my department can cover itself by providing documented proof, then the proverbial may pass us by.

As an aside, the actual software used for this system only logs the missed calls, not the journey they took.

So, onto my dilemma. Rows 3 and 5 are showing correctly at Column J. My problem lies with the middle row, when the date received is greater than the target date. Based on our office hours of 08:00-17:00, I would expect (and was hoping) the answer to show up as negative 1. But as plain as plain can be, negative 19 is showing up. Methinks all the non-working hours are also being included when this event occurs (date received greater than target date).



Column G
=9*(NETWORKDAYS(D3,G3,Holidays)-1)-24*((MOD(D3,1)-MOD(G3,1)))

Column H
=SUM(H3/24)

Column I
=IF(H3<0,"-","")&TEXT(ABS(H3)/24,"[h]:mm")


(Formulas sourced from various forums)

So, question is, how can only NETWORKDAYS be counted when date received is greater than target date?

Hope I've made all that a little understandable and I thank you for taking the time to read this post. By the way, it needs to run on Excel 2002.

Regards

Paul S

Column G has formulas that refer to Column G in it?

daffodil
Well spotted. In my haste (doctor's appointment), I deleted Column A for the PDF but forgot to correct the columns in the text.

daddylonglegs
Just amended the columns in the formula you gave to the ones I inadvertently forgot to change and it looks like it works exactly as it should do.

Even when a clown like me provides incorrect details, there are still geniuses out there able to solve these problems.

Can't thank you enough for this.

Regards

Paul S