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)

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

Any help you can offer is much appreciated.

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)

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

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
 
G

Guest

Thank you!

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

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

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
 
R

Ron Rosenfeld

Thank you!

I'm a little red faced now :blush:) 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
 
Joined
May 5, 2011
Messages
1
Reaction score
0
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. :bow:
 
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top