How to group tasks by early, on time and late completion dates

[Katie-C]

I have a report request to view tasks grouped by whether they were completed early, on time or late. I'm newer to Workfront and don't have experience with formula fields or text mode, so I'm not sure which would be an easier solution - a text mode grouping in the report or a calculated custom field that I attach to the tasks in our templates that puts out values of 'On Time,' 'Early,' or 'Late' depending on if the actual completion date was equal to, less than or greater than the planned completion date. If anyone can advise and provide the actual formula or text mode, that would be a huge help. Thank you!!

[Doug_Den_Hoed__AtAppStore]

Hi @Katie-C,

Workfront Tasks have a built in attribute called Progress Status that is very similar to what you're after, so I suggest you try it out by creating a "Progress Status" grouping as below, no textmode required.

Regards,

Doug

[Katie-C]

Hi there, 

I'm aware of Progress Status, but it does not include the requirement for tasks completed early, only late and on time.

[Doug_Den_Hoed__AtAppStore]

Right you are @Katie-C,

Using the DATEDIFF function explained here, I invite you instead to use the textmode for your Task reports grouping as below, which treats:

• incomplete Tasks as either "On Time" or "Behind" (depending upon the Progress Status) 
• Tasks whose Actual Completion Date was more than at least 1 day before the Planned Completion Date as "Early"
• Tasks whose Actual Completion Date was more than at least 1 day after the Planned Completion Date as "Late"
• Tasks whose Actual Completion Date was within +/- 1 day as "On Time"
• You can adjust the 1 and -1 to suit your own needs

group.0.iscollapsed=true
group.0.linkedname=direct
group.0.name=Duration Timeframe
group.0.valueexpression=IF(ISBLANK({actualCompletionDate}),IF({progressStatus}="OT","On Time","Behind"),IF(DATEDIFF({plannedCompletionDate},{actualCompletionDate})<-1,"Late",IF(DATEDIFF({plannedCompletionDate},{actualCompletionDate})>1,"Early","On Time")))
group.0.valueformat=HTML
textmode=true

And here's the cool part, formatted more readably (although -- harrumph -- not "pasteable" into the textmode builder):

This is an excellent teachable moment, so -- as I did -- I recommend you set up a report that looks like this:

Nothing that the definition of the first column uses this:

name=Planned vs Actual Completion
textmode=true
valueexpression=DATEDIFF({plannedCompletionDate},{actualCompletionDate})
valueformat=HTML

And the Advanced Settings for the second and third columns are turned on to show the hours.

Thanks for the challenge -- it's been out there for many years!

Regards,

Doug

[Katie-C]

That's great - thank you! Would it still work if they want early to be 1 day before the due date and late as 1 day after the due date? The way you have it setup is essentially 2 days late, correct? You said I could adjust the +1/-1, but it seems then I'd need to put 0, would that work or would break the logic?

[Doug_Den_Hoed__AtAppStore]

My pleasure @Katie-C,

Yes, that's right: at the extreme, you could change both the 1 to -1 to 0, so that anything even one minute late is late, one minute early is early, and only those rarest of "exactly" finished on time will be on time.

Regards,

Doug