Date and Time Response- before/during/after Business hours
 

Help! I'm wondering if excel has a formula for finding time differences
between 2 dates/times that span over a 24 hour period-that is spilling over
business days Here's the examples of the times I'm looking for. Business
hours are defined as 8 to 5-

I am having some difficulty with finding total response time for overdue
messages that go outside same day response:
Sent by patient after hours, replied the next day (over 24 hours) business
hours
1/9/06 6:04 PM, 1/10/06 1:43 PM
Sent by patient during business hours, replied several days (over 24 hours)
business hours
1/12/06 3:23 PM, 1/16/06 3:06 PM
Sent by patient during business hours, replied after business hours
1/12/06 3:39 PM, 1/12/06 7:49 PM
Sent by patient after business hours, replied before business hours.
1/22/06 11:02 PM, 1/23/06 7:46 AM
Sent by patient during business hours, replied during business hours.
1/22/06 12:38 PM,1/23/06 7:57 AM

any suggestion would be greatly appreciated! FYI: I tried this formula, but
it got me no where with response time being over a 24 hour (or more) span-
I'm wondering if there is a way to tweak it to cover time response over 24
hours (next business day)
=IF(AND(INT(A2)=INT(E2),NOT(ISNA(MATCH(INT(A2),$N$ 7:$N$11,0)))),0,ABS(IF(INT(A2)=INT(E2),ROUND(24*(E 2-A2),2),(24*(1700-800)*(MAX(NETWORKDAYS(A2+1,E2-1,$N$7:$N$11),0)+INT(24*(((E2-INT(E2))-(A2-INT(A2)))+(1700-800))/(24*(1700-800))))+MOD(ROUND(((24*(E2-INT(E2)))-24*800)+(24*1700-(24*(A2-INT(A2)))),2),ROUND((24*(1700-800)),2))))))

THanks!
--
Erin

Date and Time Response- before/during/after Business hours
 

I would suggest perusing Chip Pearson's web site at the following URL:

ww.cpearson.com/excel/datearith.htm
--
Kevin Backmann


"ECLynn" wrote:

Help! I'm wondering if excel has a formula for finding time differences
between 2 dates/times that span over a 24 hour period-that is spilling over
business days Here's the examples of the times I'm looking for. Business
hours are defined as 8 to 5-

I am having some difficulty with finding total response time for overdue
messages that go outside same day response:
Sent by patient after hours, replied the next day (over 24 hours) business
hours
1/9/06 6:04 PM, 1/10/06 1:43 PM
Sent by patient during business hours, replied several days (over 24 hours)
business hours
1/12/06 3:23 PM, 1/16/06 3:06 PM
Sent by patient during business hours, replied after business hours
1/12/06 3:39 PM, 1/12/06 7:49 PM
Sent by patient after business hours, replied before business hours.
1/22/06 11:02 PM, 1/23/06 7:46 AM
Sent by patient during business hours, replied during business hours.
1/22/06 12:38 PM,1/23/06 7:57 AM

any suggestion would be greatly appreciated! FYI: I tried this formula, but
it got me no where with response time being over a 24 hour (or more) span-
I'm wondering if there is a way to tweak it to cover time response over 24
hours (next business day)
=IF(AND(INT(A2)=INT(E2),NOT(ISNA(MATCH(INT(A2),$N$ 7:$N$11,0)))),0,ABS(IF(INT(A2)=INT(E2),ROUND(24*(E 2-A2),2),(24*(1700-800)*(MAX(NETWORKDAYS(A2+1,E2-1,$N$7:$N$11),0)+INT(24*(((E2-INT(E2))-(A2-INT(A2)))+(1700-800))/(24*(1700-800))))+MOD(ROUND(((24*(E2-INT(E2)))-24*800)+(24*1700-(24*(A2-INT(A2)))),2),ROUND((24*(1700-800)),2))))))

THanks!
--
Erin