Filtering on WeekDayDiff

Dear Tracy, I suspect there are two culprits at play here. One is that perhaps the difference in the date fields is a pure datediff and not working day different. The second is that it's including hours (so that a task scheduled to finish at 2:00 pm on 1/1/18 and actually finished at 4:00 pm on 1/1/18 is producing a value less than 1 but greater than 0, then rounding that to show 0 but get caught in the filter). Two approaches (or perhaps they'll combine into one). The first is to muck around with tweaking the formula. The other is to create a calculated field on a task form for you weekdaydiff value, then simply filter on that in the report. The downside of the latter is you need to attach the form to a task before it will show. If you're running purely off template tasks (or only want the report for milestone tasks that are in the template) that shouldn't be a problem, but if it's any task you could have valid tasks missed by the report because the form wasn't attached. As a side note, while actual start/completion dates are pre-populated when you 'work on' / complete tasks, they can be manually over-written if you didn't update the task at the exact moment you started/finished. I'll play around with a filter formula out of curiosity and see if I can crack it. (By the way, congrats on poking around and finding the FIELD: option in filters. I found that in a post from Melinda Layten and it fair blew my mind). Barry Buchanan Work Management Australia

Thanks Barry for your input. Any additional thoughts or findings would be appreciated while I continue to improve on this reporting. Tracy Fox Project Manager / Workfront SysAdmin Sykes Assistance Services Corporation

I haven't forgotten this. Here's possibly more information than you ever wanted on dates. I created a report showing planned completion date and actual completion date. I then compared dateDiff with weekdayDiff and also added rounding on both of those and for good measure, included a field in the API that isn't in the interface - daysLate. The first thing to note between the two is the reversal of sign. If a task is finished early, it has a negative dateDiff but a positive weekdayDiff and vice versa. The other is that without dateDiff isn't rounded, (i.e. a task finished the day it was due but an hour late has a non zeor (0.04) dateDiff value), but even without a rounding function weekdayDiff returns whole numbers. So the two aren't just interchangeable. daysLate looks like the real hero here - it seems to have everything you'd want. Unfortunately, referencing it in a filter caused a red error message when you tried to run the report. Likewise, any attempt to round the date formulas, or even do a comparison adding a day to the actual completion date failed for me. I can only recommend using a calculated field on a task custom form, attaching the form to all your template tasks and having a separate report for all tasks on active projects where the value is blank (indicating someone added a task without attaching the form) for the PMO to run (and use to attach the form) prior to running the late report. Barry Buchanan Work Management Australia

Barry Thank you so much for taking the time to run these comparisons for me. It is greatly appreciated. For us as well, it seems that daysLate is the real winner as well. We do export our reports to Excel for final use, so if we can't get the filter to work either (as you noted), then we can still manage. I feel pretty confident with my current report building skills using textmode, but haven't explored the use of the API. Could you provide some guidance on how you created the daysLate field within the API? From there I will put my learning curve to the test! Thanks again! Tracy Fox Project Manager / Workfront SysAdmin Sykes Assistance Services Corporation

Dear Tracy, My apologies for the confusion. I didn't (and can't) create the field in the API - I found it in the API documentation: "https://support.workfront.com/hc/en-us/articles/220633507-API-Explorer/support.workfront.com/hc/en-us/articles/220633507-API-Explorer" https://support.workfront.com/hc/en-us/articles/220633507-API-Explorer (Filter on 'Task', and then scroll through the alphabetically sorted list to 'Days Late' All I know about it is that it's field type is integer - I don't know the formula / logic behind it, but looking at the name and output indicates it does what it says on the box. I can include it as a column in a report without going into text mode, but it isn't available as a choice on the filter page, and putting another integer field in the filter, changing to text mode and replacing the filed name with daysLate doesn't work. Regards, Barry Buchanan Work Management Australia

Ah thank you still! This points me in the right direction :) Tracy Fox Project Manager / Workfront SysAdmin Sykes Assistance Services Corporation

Barry, Just wanted to let you know that I tried to get assistance from Support as to why dayslate can't be used in a report filter. My efforts got me a "submit a feature request through the Idea exchange" answer. So I have added one: "https://support.workfront.com/hc/en-us/community/posts/115007437773-Using-Days-Late-dayslate-in-report-filter">Using-Days-Late-dayslate-in-report-filter . If you wouldn't mind helping to upvote it it would be greatly appreciated :) Tracy Fox Project Manager / Workfront SysAdmin Sykes Assistance Services Corporation

Done. Looks like it's already getting a bit of interest. Good luck! Barry Buchanan Work Management Australia