Conditional Formatting for Calculated Column

[Knelis13]

Hi,

I tried referencing this community article but couldn't make it work for my situation. I will say I am a total beginner at text mode so I'm just trying to figure out what to switch around in someone else's code to fit my situation. Also if anyone knows if there's now a way to create a chart based on a calculated column, that would be amazing!

I'm trying to add conditional formatting to a calculated column in a report.

2 questions:

1) I used another community article that I can't find at the moment to calculate the difference in days between planned and actual completion dates. How can I change the calculation so the result returns a whole integer for days instead of decimals?

2) I want to be able to use 1 color to highlight when something was completed more than 1 week before the planned completion date and a 2nd color to highlight when something was completed more than 1 week after the planned completion date. Copied below is the text mode I created trying to merge the two solutions I found earlier. If anyone is able to help, that would be awesome! Thanks in advance 🙂

displayname=Planned-Actual Completion Date
linkedname=direct
querysort=plannedCompletionDate
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.isrowcase=false
styledef.case.0.comparison.leftmethod=direct
styledef.case.0.comparison.lefttext=direct
styledef.case.0.comparison.operator=gt
styledef.case.0.comparison.operatortype=date
styledef.case.0.comparison.righttext=7 days
styledef.case.0.comparison.trueproperty.0.name=textcolor
styledef.case.0.comparison.trueproperty.0.value=000000
styledef.case.0.comparison.trueproperty.1.name=bgcolor
styledef.case.0.comparison.trueproperty.1.value=ED9FA5
styledef.case.1.comparison.icon=false
styledef.case.1.comparison.isrowcase=false
styledef.case.1.comparison.leftmethod=direct
styledef.case.1.comparison.lefttext=direct
styledef.case.1.comparison.operator=lt
styledef.case.1.comparison.operatortype=date
styledef.case.1.comparison.righttext=-7 days
styledef.case.1.comparison.trueproperty.0.name=bgcolor
styledef.case.1.comparison.trueproperty.0.value=F0BC99
valueexpression=ROUND(DATEDIFF({plannedCompletionDate},{actualCompletionDate}),2)
valueformat=HMTL

[IvanBebek-iX]

Hi @Knelis13,

Before looking into this in more details I have noticed that you are missing the textmode=true option, could you please add this and let me know if it works? 

Thank you in advance.

Best regards,
Ivan

[NicholeVargas]

@Knelis13 You'd be better off creating calculated custom fields vs using text mode expressions, as then you'll be able to reference these fields in other reports or in charts. For example, your calculated custom field (format = Number, this is important) would be the following:

ROUND(DATEDIFF({actualCompletionDate},{plannedCompletionDate}),0)

That rounds the value to the nearest whole number vs decimal places. You can then create another calculated custom field using that value to determine if it's 1 week before or after.

The article, Calculated Data Expressions, has more information on the ROUND and DATEDIFF expressions along with others that you can use for your secondary calculation, likely IF statements.