This is pretty awesome
This works very well!
Thank you very much for sharing this.
You'd think this would be a built in feature by now...we are version 10.
What wasn't totally clear to me and took some time to figure out...the [FirstResponse] field you reference to...for me is actually the EndTime in my data set. I think someone else mentioned that it looked like your formulas were trying to capture response times, not so much ticket duration time, but it comes to the same thing, you just replace the [FirstResponse] field with the [EndTime] and it's all good!
Example output for my scenario where I want to exclude all non-business hours outside of weekdays 9AM-5PM:
|Outage Example||StartTime||EndTime||Expected Hours (BH 9-5)||Business Hours Calculated (hrs)|
|2 weekdays||11/20/2013 12:00:00 PM||11/22/2013 12:00:00 PM||16||16|
|Afternoon||11/21/2013 12:00:00 PM||11/21/2013 5:00:00 PM||5||5|
|over multiple weekends||11/22/2013 3:00:00 PM||12/02/2013 10:00:00 AM||43||43|
|over weekend||11/22/2013 3:00:00 PM||11/25/2013 10:00:00 AM||3||3|
|Overnight weekday||11/20/2013 12:00:00 PM||11/21/2013 11:00:00 AM||7||7|
|same day||11/20/2013 9:00:00 AM||11/20/2013 4:00:00 PM||7||7|
Now...to implement this into our workbook...the fun begins! :-D