Will this calculate over 24 hours? I tried to put like 6 days difference in
both cells and it came back with 9 hours.

"Mike H" wrote:

Hi,

There may be a formula to do it but how about this workaround. Use the formula

=(NETWORKDAYS(MIN(G3,I3),MAX(G3,I3))-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(MAX(G3,I3),MAX(G3,I3)) ,MEDIAN(MOD(MAX(G3,I3),1),TIME(17,0,0),TIME(8,0,0) ),TIME(17,0,0))-MEDIAN(NETWORKDAYS(MIN(G3,I3),MIN(G3,I3))*MOD(MIN( G3,I3),1),TIME(17,0,0),TIME(8,0,0))

Then for the cell where the formula is apply a conditional format of
=G3I3
Apply a colour of (say) green

Now if your deal close early it will still show up as a positive number but
the cell colour will change to whatever colour you set.

Mike


"drew" wrote:

This looks great.. Is there any way to display the negative numbers?

"Mike H" wrote:

Try

=(NETWORKDAYS(G3,I3)-1)*(TIME(17,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1 ),TIME(17,0,0),TIME(8,0,0)),TIME(17,0,0))-MEDIAN(NETWORKDAYS(G3,G3)*MOD(G3,1),TIME(17,0,0),T IME(8,0,0))

This wouldn't work if a deal closed early so post back if that's an issue.

Mike

"drew" wrote:

I am trying to count the difference between two fields which are date/time to
show if we met our service level agreements. However, I don't want to count
non business hours.

Business Hours are 8-5.

For example

Cell G3 is the date customer wants to close the deal - 6/23/2008 9:00 AM

Cell I3 is actual closing date 6/23/2008 10:45 AM

I would expect to see (1:45)

Can anyone help? Thanks!