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? )
Any help you can offer is much appreciated.
Scott
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