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!
Solved! Go to Solution.
Views
Replies
Total Likes
Huh. That's a surprisingly tough challenge @Kiersten_K,
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
Huh. That's a surprisingly tough challenge @Kiersten_K,
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
Thanks, Doug.
I think I can work with this. I appreciate the assist. I will follow up if I can get this to work.
I was able to make this work around work for the report we were looking for.
Thanks again!
Views
Replies
Total Likes
Excellent -- thanks for letting me know, @Kiersten_K.
Having custom forms at the task level can seem a bit heavy sometimes, but it often does the trick.
Regards,
Doug
Hi there! Another option here - you could always just change the column format of your actual completion date column. Have a task report with an actual completion date column, and in the advanced options, choose to show the date AND time:
Then you could always sort to see after hours completion within your 3-month window filter...it's not a filter option but at least gives you the info on date and time in your report.
Views
Replies
Total Likes
Views
Like
Replies
Views
Likes
Replies