Hello,
I have been tasked to setup a metric that measures how long it takes for our unit reply to a customer's online web request.
Our hours of operation is Monday thru Friday from 8 AM to 6:30 PM; note weekends, holidays, and outside of our business hours should not be counted in the metric. We have been using the following logic and formula as illustrated below; however we have noticed that if the StartDT falls on a weekend or outside of business hours that the metric is counting from that starting point instead of waiting to start ticking on the next business day at the start of the shift.
A customer can submit a online web request on any day and at any time.
Using the Name manager: Hours of operation Monday to Friday 8 AM to 6:30 PM
'DayStart' (the time of day that the normal work day begins; 8 AM)
'DayEnd' (the time of day that the normal work day ends; 6:30 PM)
'HolidayList' (A range containing a list of dates to exclude holidays.)
'StartDT' (The starting date and time.)
'EndDT' (The ending date and time.)
Formula to measure hours between two timestamps; using the name manager as a reference:
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
Worksheet Functions For Dates and Times:
Worksheet Functions For Dates And Times
Example: Using the formula above; we have been getting our # of hours and then converting to minutes by dividing the number of hours by 24 and changing the format to custom [m] in another column as illustrated under # of minutes. Only the first record sample is accurate. We can really use your expertise to get this where it only measures the number hours between hours of operations on a business day (excluding weekends/holidays/outside of operating hours of business). Any suggestions welcomed, please and thank you?
<tbody>
</tbody>
I have been tasked to setup a metric that measures how long it takes for our unit reply to a customer's online web request.
Our hours of operation is Monday thru Friday from 8 AM to 6:30 PM; note weekends, holidays, and outside of our business hours should not be counted in the metric. We have been using the following logic and formula as illustrated below; however we have noticed that if the StartDT falls on a weekend or outside of business hours that the metric is counting from that starting point instead of waiting to start ticking on the next business day at the start of the shift.
A customer can submit a online web request on any day and at any time.
Using the Name manager: Hours of operation Monday to Friday 8 AM to 6:30 PM
'DayStart' (the time of day that the normal work day begins; 8 AM)
'DayEnd' (the time of day that the normal work day ends; 6:30 PM)
'HolidayList' (A range containing a list of dates to exclude holidays.)
'StartDT' (The starting date and time.)
'EndDT' (The ending date and time.)
Formula to measure hours between two timestamps; using the name manager as a reference:
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
Worksheet Functions For Dates and Times:
Worksheet Functions For Dates And Times
Example: Using the formula above; we have been getting our # of hours and then converting to minutes by dividing the number of hours by 24 and changing the format to custom [m] in another column as illustrated under # of minutes. Only the first record sample is accurate. We can really use your expertise to get this where it only measures the number hours between hours of operations on a business day (excluding weekends/holidays/outside of operating hours of business). Any suggestions welcomed, please and thank you?
| Day | StartDT | EndDT | # of Hours | # of Minutes | Accuracy |
| Thur | 1/16/2014 12:27 | 1/16/2014 12:30 | 0.05 | 3 | accurate |
| Sat | 1/11/2014 22:26 | 1/13/2014 8:03 | 3.88 | 232 | inaccurate |
| Sun | 2/9/2014 13:51 | 2/10/2014 7:02 | 3.69 | 221 | inaccurate |
| Tue | 5/27/2014 22:02 | 5/28/2014 8:51 | 0.68 | 40 | inaccurate |
| Wed | 5/28/2014 22:08 | 5/29/2014 7:16 | 4.37 | 262 | inaccurate |
<tbody>
</tbody>
