I built a project report and pulled in two task "actual completion dates" as columns into this report. I am now trying to create a third column that shows the difference of work days between these dates. | Community
Skip to main content
J
JKorp
Level 2
February 2, 2022
Question

I built a project report and pulled in two task "actual completion dates" as columns into this report. I am now trying to create a third column that shows the difference of work days between these dates.

  • February 2, 2022
  • 3 replies
  • 2449 views

Here is the syntax from pulling in the task dates into columns:

displayname=Creative Approved Date

listmethod=nested(tasks).lists

textmode=true

type=iterate

valueexpression=IF({name}="Receive Client Content Approval",{actualCompletionDate})

valueformat=HTML

displayname=Program Launch Date

listmethod=nested(tasks).lists

textmode=true

type=iterate

valueexpression=IF({name}="Start program outreach",{actualCompletionDate})

valueformat=HTML

And here is the syntax that I'm trying to get the week day difference between these two columns (but it's not working):

aggregator.function=AVG

aggregator.namekey=datevariance

aggregator.valueexpression=WEEKDAYDIFF(IF({name}=“Start program outreach”,{actualCompletionDate}),IF({name}=“Receive Client Content Approval”,{actualCompletionDate}))

aggregator.valueformat=HTML

displayname=Days from creative approval to launch

namekey=datevariance

textmode=true

valueexpression=ABS(WEEKDAYDIFF(IF({name}=“Start program outreach”,{actualCompletionDate}),IF({name}=“Receive Client Content Approval”,{actualCompletionDate}))

valuefield=dateVariance

valueformat=HTML

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

3 replies

imgrund
Adobe Employee
imgrundAdobe Employee
Adobe Employee
February 2, 2022

Hi Jeannine - I'd be interested in seeing what other people have to say on this.

Part of the issue is that a collection (what you are using for Creative Approved Date and Program Launch Date) is not a single numeric value and you can't do a calculation on two fields that are not a single numerical value. The collection is literally looking at every single task in the project, it just showing the Actual Completion Dates for the task(s) that fit your IF criteria.

You would need to have the two dates as actual data points on the custom form to then do WEEKDAYDIFF and get an average. Something like Fusion or a custom API call could help with this. But otherwise, I'm not sure it can be done.

Fingers crossed someone has an idea to help you :)

    B
    BenettaPe
    Level 9
    October 11, 2022

    Anthony,

     

    Are you aware of a solution or workaround for this problem?  I need this same solution.

     

    Thanks

    Doug_Den_Hoed__AtAppStore
    Community Advisor
    Doug_Den_Hoed__AtAppStoreCommunity Advisor
    Community Advisor
    February 2, 2022

    Hi Jeannine (and Anthony),

    To measure the delta between two tasks on a project, I invite you to consider my Going The Extra Milestone technique.

    If you implement it, the third column you seek will be the duration of the new (special) Project Summary Task you'll create, and look for.

    Regards,

    Doug

    B
    BenettaPe
    Level 9
    October 11, 2022

    Doug,

    How much does your solution cost?  Thanks.

    Doug_Den_Hoed__AtAppStore
    Community Advisor
    Doug_Den_Hoed__AtAppStoreCommunity Advisor
    Community Advisor
    October 11, 2022

    Hi Benetta,

     

    It looks like you're asking about my Going The Extra Milestone technique, which anyone can set up and put to use since it's based on built in Workfront features.

     

    If, on the other hand, you'd like something fancier or more automatic, we do have 50+ solutions in our www.atappstore.com catalog, about which I'm always happy to chat further via doug.denhoed@atappstore.com

     

    Regards,

    Doug

    B
    BenettaPe
    Level 9
    October 11, 2022

    Jeannine,

     

    Did you ever get a working solution for this problem? I need to do the same thing.

     

    Thanks,

      Jason_JB
      Jason_JB
      Level 5
      October 12, 2022

      We’ve approached this type of need using Fusion to harvest the date from the task and write it to a custom field on the project. 

      In our scenario we sometimes need to go further than just displaying a single date and have the system identify the first/last of a specific type of task. We do this for both start of build and golives. Fusion does the analysis and writes it to the project. 

      B
      BenettaPe
      Level 9
      October 12, 2022

      Jason,

      Thank you.

      Terms & ConditionsAccessibility statement

      Loading footer...