Expand my Community achievements bar.

# Workfront

SOLVED

## After Hours Completed Tasks Report?

Level 8

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!

1 Accepted Solution

Level 10

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

5 Replies

Level 10

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

Level 8

Thanks, Doug.

I think I can work with this. I appreciate the assist. I will follow up if I can get this to work.

Level 8

I was able to make this work around work for the report we were looking for.

Thanks again!

Level 10

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