Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine Date & Time Cells | Excel Discussion (Misc queries) | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions |