Calculating networkdays & working hours
 

Hi,

Some time back, someone with much more Excel knowledge than I have posted a
formula on this site to help me work out the number of working hours between
2 dates/times - the time an e-mail was received and then answered.

The formula I'm using is:
=(NETWORKDAYS(A2,B2)-1)*(I$3-I$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),I$3,I$ 2),I$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),I$3,I$2)

A2 = date/time received
B2 = date/time answered
I3 = end of business day
I2 = start of business day

I've come across a situation that this formula doesn't seem to like and that
is if the response date is more than 2 business days after the receipt date.

The example I have is:

Received: 27/03/2007 12:08:00 PM
Answered: 30/03/2007 11:42:51 AM

This returns a result of 01:04:51 work hours (no publc holidays).

If I change the received date to 28/03/2007 12:08:00 PM, I receive a result
of 16:34:51 which is correct.

Can someone help with this as I don't understand the original formula well
enough to work on an enhancement? :o)

Any help you can offer is much appreciated.

Scott

Calculating networkdays & working hours
 

On Tue, 7 Aug 2007 19:20:03 -0700, Scopar
wrote:

Hi,

Some time back, someone with much more Excel knowledge than I have posted a
formula on this site to help me work out the number of working hours between
2 dates/times - the time an e-mail was received and then answered.

The formula I'm using is:
=(NETWORKDAYS(A2,B2)-1)*(I$3-I$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),I$3,I$ 2),I$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),I$3,I$2)

A2 = date/time received
B2 = date/time answered
I3 = end of business day
I2 = start of business day

I've come across a situation that this formula doesn't seem to like and that
is if the response date is more than 2 business days after the receipt date.

The example I have is:

Received: 27/03/2007 12:08:00 PM
Answered: 30/03/2007 11:42:51 AM

This returns a result of 01:04:51 work hours (no publc holidays).

If I change the received date to 28/03/2007 12:08:00 PM, I receive a result
of 16:34:51 which is correct.

Can someone help with this as I don't understand the original formula well
enough to work on an enhancement? :o)

Any help you can offer is much appreciated.

Scott

Most likely you need to format your answer as:

Format/Cells/Number/Custom Type: [h]:mm:ss


--ron

Calculating networkdays & working hours
 

Thank you!

I'm a little red faced now :o) I can't believe it was that simple ...

"Ron Rosenfeld" wrote:

On Tue, 7 Aug 2007 19:20:03 -0700, Scopar
wrote:

Hi,

Some time back, someone with much more Excel knowledge than I have posted a
formula on this site to help me work out the number of working hours between
2 dates/times - the time an e-mail was received and then answered.

The formula I'm using is:
=(NETWORKDAYS(A2,B2)-1)*(I$3-I$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),I$3,I$ 2),I$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),I$3,I$2)

A2 = date/time received
B2 = date/time answered
I3 = end of business day
I2 = start of business day

I've come across a situation that this formula doesn't seem to like and that
is if the response date is more than 2 business days after the receipt date.

The example I have is:

Received: 27/03/2007 12:08:00 PM
Answered: 30/03/2007 11:42:51 AM

This returns a result of 01:04:51 work hours (no publc holidays).

If I change the received date to 28/03/2007 12:08:00 PM, I receive a result
of 16:34:51 which is correct.

Can someone help with this as I don't understand the original formula well
enough to work on an enhancement? :o)

Any help you can offer is much appreciated.

Scott

Most likely you need to format your answer as:

Format/Cells/Number/Custom Type: [h]:mm:ss


--ron

Calculating networkdays & working hours
 

On Tue, 7 Aug 2007 21:44:10 -0700, Scopar
wrote:

Thank you!

I'm a little red faced now :o) I can't believe it was that simple ...


Well, I'm glad the fix was that easy. It is a common mistake to make.
--ron