Calculated Column Help - looking for a date difference between a custom field and actual completion date

[KristinaWa1]

Hello,

I have a pacing reporting I am trying to create. It is a task report that is looking for our certain tasks we consider milestones for revenue recognition. 

I have the report set up pulling in all the columns I want, but I am having trouble getting the correct formula for text mode in a column.

I have two field I want to find the duration of. I have the Task Actual Completion Date and then a custom field housed on the project level called Sign Date. So I am trying figure out how much time it took from signature, "Sign Date", till the task was completed. I have tried using the below but no results are showing.

Thank you and I appreciate any help!

Kristina

valueexpression=CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate),2)," Days")

valuefield=DE:Sign Date

linkedname=direct

valueformat=HTML

aggregator.valueexpression= CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate),2)," Days")

aggregator.function=SUM

aggregator.valueformat=HTML

aggregator.displayformat=HTML

displayname=Milestone Pacing

namekey=$$TODAY

textmode=true

[Doug_Den_Hoed__AtAppStore]

Hi @KristinaWa1,

Texmode expects either valuefield or valueexpression (but not both), so I suggest you try removing the former so the latter kicks in.

valueexpression=CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate),2)," Days")

valueformat=HTML

aggregator.valueexpression= CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate),2)," Days")

aggregator.function=SUM

aggregator.valueformat=HTML

aggregator.displayformat=HTML

displayname=Milestone Pacing

textmode=true

Regards,

Doug

[ajdifonzo]

As usual, I agree with Doug, but you are also missing a bracket, I added and highlighted in red below.  

valueexpression=CONCAT(ROUND(DATEDIFF({DE:Sign Date},{actualCompletionDate}),2)," Days")

[KristinaWa1]

Thank you @Doug_Den_Hoed__AtAppStore and @ajdifonzo ,

I used that formula with the added missing bracket, and I am not getting a full answer back. They are all saying just "Days" with no actual number added even if both fields have dates listed. Is there something I need to add in as the Sign Date is on the project level and the actual completion is on the task level? I tried added in "project." to the Sign Date in the formula that them removed the Days from the column.

Kristina

[ajdifonzo · Accepted solution]

I should have asked this to start with, what type of report are you creating?  If this is a Task report, then yes you will need to add {project}. 

valueexpression=CONCAT(ROUND(DATEDIFF({project}.{DE:Sign Date},{actualCompletionDate}),2)," Days")

[KristinaWa1]

Thank you, that worked! I forgot the brackets around project. I appreciate the help!

And for anyone that comes across this also looking for help, I did make one update and used the following so that I was only pulling in Week Days.

aggregator.displayformat=HTML
aggregator.function=SUM
aggregator.valueexpression=CONCAT(ROUND(WEEKDAYDAYDIFF({project}.{DE:Sign Date},{actualCompletionDate}),2)," Days")
aggregator.valueformat=HTML
displayname=Milestone Pacing
textmode=true
valueexpression=CONCAT(ROUND(WEEKDAYDIFF({project}.{DE:Sign Date},{actualCompletionDate}),2)," Days")
valueformat=HTML