Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!

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.

Avatar

Level 2

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

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

10 Replies

Avatar

Level 10

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 :)

Avatar

Level 2

Anthony,

 

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

 

Thanks

Avatar

Community Advisor

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

Avatar

Community Advisor

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

Doug,

 

Nothing fancy needed. I will read your Go the Extra Milestone technique article.

Avatar

Level 2

Jeannine,

 

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

 

Thanks,

Avatar

Level 7

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. 

Avatar

Level 3

I too have found desire for being able to take time difference between two date fields to automatically calculate the turn-around-time for issues or tasks.