Calculating networkdays & working hours

[Scopar]

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? )

Any help you can offer is much appreciated.

Scott

 

[Ron Rosenfeld]

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? )

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

 

[Scopar]

Thank you!

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

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? )

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

 

[Ron Rosenfeld]

Thank you!

I'm a little red faced now ) 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