# Calculating networkdays & working hours

G

#### Guest

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)

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:

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

Scott

R

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

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:

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

Scott

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

--ron

G

#### Guest

Thank you!

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

Ron Rosenfeld said:
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)

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:

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

Scott

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

--ron

R

#### 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

#### MokTakTin

How do I include WeekEnd for Saturday and Sunday with a different set of working hours 9:00am to 9:00pm? Normal business hours is 7:00 am to 10:00pm. (Using the same formula above by Ron).

Any help is appreaciate.

Last edited: