Need help adding conditional formatting to a custom column with a custom calculated valueexpression

[KyleFr2]

So essentially I created a valueexpression that calculates the difference between the entry date and the actual completion date and returns a "## days" copy. I'm hoping to add custom formatting (cell color and text color) for any returns greater than both 30 and 90 days. I can't figure out how to align the comparison left and right text to return that outcome. Here is the code I have so far:

displayname=Entry-Actual Completion Date
linkedname=direct
querysort=plannedCompletionDate
styledef.case.1.comparison.icon=false
styledef.case.1.comparison.leftmethod=direct
styledef.case.1.comparison.lefttext=direct
styledef.case.1.comparison.operator=gt
styledef.case.1.comparison.operatortype=date
styledef.case.1.comparison.righttext=90 Days
styledef.case.1.comparison.trueproperty.0.name=textcolor
styledef.case.1.comparison.trueproperty.0.value=d30519
styledef.case.1.comparison.trueproperty.1.name=bgcolor
styledef.case.1.comparison.trueproperty.1.value=eac6c9
styledef.case.1.comparison.trueproperty.2.name=align
styledef.case.1.comparison.trueproperty.2.value=left
styledef.case.1.comparison.truetext=
styledef.case.2.comparison.icon=false
styledef.case.2.comparison.leftmethod=direct
styledef.case.2.comparison.lefttext=direct
styledef.case.2.comparison.operator=gt
styledef.case.2.comparison.operatortype=date
styledef.case.2.comparison.righttext=30 Days
styledef.case.2.comparison.trueproperty.0.name=textcolor
styledef.case.2.comparison.trueproperty.0.value=e19503
styledef.case.2.comparison.trueproperty.1.name=bgcolor
styledef.case.2.comparison.trueproperty.1.value=feecc8
styledef.case.2.comparison.truetext=
textmode=true
valueexpression=CONCAT(ROUND(DIV(DATEDIFF({actualCompletionDate},{entryDate}),2),0)," Days")
valueformat=HTML

[skyehansen]

I'd be interested to hear how you came up with this, especially the leftmethod and lefttext lines. Can I instead talk you into putting a calculated field on all your objects and doing your calculation in there, and formatting it as a number? (leave off the days text for the time being... maybe if you can get the field comparison to work, you can put in a valueexpression to add " days")

[KyleFr2]

Hi Skye,

I apologize, that was an earlier attempt to try and work out the issue, I pulled the "direct" from the linkedname field. I'll look into the calculation option and see if I can find a workaround, otherwise it's not the end of the world if that data can't have rules and formatting. Thanks!

[skyehansen · Accepted solution]

ah ok. That explains it. I'm sure this isn't right but my brain always translates the "leftmethod" and "lefttext" stuff as "here's the field we're using on the left" and so I was a bit surprised to see that "direct" might work (I guess it doesn't, lol). And so that's why I'm also recommending a calculated field -- probably the best way to get something that works in that space so you can do some formatting to see if you want to commit to that kind of thing.