Help with text mode to show plannedCompletionDate minus two days if task name contains "and Approval"

[AndrewGr1]

I am wondering if anyone could help figure out if this is possible and can help with the text mode code to have a column in a task or assignment report show the plannedCompletionDate minus two days whenever the task name contains "and Approval" to provide a better due date for users to allow two days for task approvals to go through since we can't build in durations for task approval loops once a task is completed. 

Thank you!

[WeberJ · Accepted solution]

@AndrewGr1 To return this date, you could add a text mode column: 

displayname=Text Edit
textmode=true
valueexpression=IF(CONTAINS("Approval",{name})=true,ADDDAYS({plannedCompletionDate},-2),{plannedCompletionDate})
valueformat=HTML

Or you could pull out the expression and use it in a calculated field:

IF(CONTAINS("Approval",{name})=true,ADDDAYS({plannedCompletionDate},-2),{plannedCompletionDate})

[AndrewGr1]

Thank you so much! That appears to be working well for task reports. I had trouble manipulating it to work in an assignment report instead, though.

Would you happen to know how to get that same code to work in an assignment report?

[AndrewGr1]

@WeberJ  And I guess if it's possible, it would be even better to show that adjusted due date as a column based on if there is a task approval, if that logic is possible rather than relying on how we name the tasks, which isn't always consistent. 

[WeberJ]

@AndrewGr1 Happy to assist! Using text mode in an assignment report would be slightly more complicated; perhaps someone else has a suggestion on how to return to that calculation. 

Regarding tasks with approvals but missing "approval" in the name, I updated the previous expression here:

IF(CONTAINS("Approval",{name})||IF(ISBLANK({approvalProcessID})=false,true,false)=true,ADDDAYS({plannedCompletionDate},-2),{plannedCompletionDate})

I used these two experience league pages to help build these text mode solutions: 

• Text mode syntax overview
• Overview of calculated data expressions

[AndrewGr1]

This is what I ended up using for now in my task report column to reduce due date by 1 day instead of 2 for now:
displayname=(APPROVAL) Adjusted Due Date -1 day
shortview=true
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.leftmethod=approvalProcessID
styledef.case.0.comparison.lefttext=approvalProcessID
styledef.case.0.comparison.operator=notblank
styledef.case.0.comparison.operatortype=string
styledef.case.0.comparison.trueproperty.0.name=textcolor
styledef.case.0.comparison.trueproperty.0.value=e19503
styledef.case.1.comparison.icon=false
styledef.case.1.comparison.leftmethod=plannedCompletionDate
styledef.case.1.comparison.lefttext=ADDDAYS({plannedCompletionDate}, -1)
styledef.case.1.comparison.operator=lte
styledef.case.1.comparison.operatortype=date
styledef.case.1.comparison.righttext=$$TODAY+1d
styledef.case.1.comparison.trueproperty.0.name=textcolor
styledef.case.1.comparison.trueproperty.0.value=d30519
styledef.case.1.comparison.truetext=
textmode=true
valueexpression=IF(ISBLANK({approvalProcessID})=false, CONCAT(ADDDAYS({plannedCompletionDate}, -1)), "")
valueformat=HTML