How to group tasks by early, on time and late completion dates | Community
Skip to main content
K
Katie-C
Level 2
September 15, 2023
Question

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

  • September 15, 2023
  • 1 reply
  • 1204 views

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!!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

Doug_Den_Hoed_AtAppStore
Community Advisor
Doug_Den_Hoed_AtAppStoreCommunity Advisor
Community Advisor
September 15, 2023

 

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

 

 

K
Katie-CAuthor
Level 2
September 19, 2023

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
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    September 19, 2023

     

    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

     

    Terms & ConditionsAccessibility statement

    Loading footer...