Our team is trying to understand the volume of tasks being completed after working hours, specifically looking at anything completed between 5 pm - 8 am. We want to be able to pull a 3 month window of time into the report at any given time (not targeting specific dates).
Is it possible to pull a specific hour range for tasks? Could this be done by using text mode?
We are hoping to avoid Excel if we can.
Thanks!
Huh. That's a surprisingly tough challenge @kierstenkollins,
To my knowledge, there is no way to natively filter by HOUR(actualCompletionDate) -- even with an EXISTS -- and a matrix report only goes down to the Day level, where you'd need "to the hour" (the idea then being to highlight and sum those tasks completed outside of business hours...which would still then take some extra math).
If you have (or add) a custom form on every task of interest, you could add a calculated custom parameter called Actual Completion After Hours with a formula of
IF(ISBLANK({actualCompletionDate}),"To Do",IF(HOUR({actualCompletionDate})> 8 && HOUR({actualCompletionDate}) < 17,"No","Yes"))
With that in place (taking care to recalculate custom data expressions first to ensure the data is populated), you could then filter for Tasks whose Actual Completion Date is greater than $$NOW-90d (i.e. the beginning of the month minus 90 days), and filter by Actual Completion After Hours = "Yes", and/or group (whether regular or matrix) by Actual Completion After Hours, and or chart the data (e.g. a bar chart counting the number of Tasks by Actual Completion Date Week, stacked by Actual Completion After Hours...it's a candy store.
One cavity-it, though...in addition to the usual test-this-out-first cautions, if you have users in multiple time zones, some additional care and feeding (e.g. "who's HOUR get's populated) is likely in order.
Have fun -- I'm interested to hear how you make out!
Regards,
Doug
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.