Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

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.

0 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 10

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