Expand my Community achievements bar.

SOLVED

Less than function when comparing two dates - Planned Completion date and a custom date does not work

Avatar

Level 1

Hi-

 

I'm trying to compare two dates in a Workfront report via conditional formatting.  Whenever the target launch date is less than the Planned Completion Date, the background for the Target Launch date item should be in red. When the report loads, the background for the dates that are less than AND equal to are in red. I suspect the issue is that my Target Launch date is a custom field with a value format of DateAsString whereas Planned Completion Date contains both a date and time element.  Below is what I have so far, any idea how to prevent background for the identical dates from being in red?

 

linkedname=direct
namekey=Target Launch Date
querysort=DE:Target Launch Date
styledef.case.0.comparison.icon=false
styledef.case.0.comparison.leftmethod=DE:Target Launch Date
styledef.case.0.comparison.lefttext=DE:Target Launch Date
styledef.case.0.comparison.operator=lt
styledef.case.0.comparison.operatortype=date
styledef.case.0.comparison.rightmethod=plannedCompletionDate
styledef.case.0.comparison.righttext=FIELD:plannedCompletionDate
styledef.case.0.comparison.trueproperty.0.name=bgcolor
styledef.case.0.comparison.trueproperty.0.value=eac6c9
styledef.case.0.comparison.truetext=
textmode=false
valuefield=Target Launch Date
valueformat=customDateAsString

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

I wonder if the time of day is throwing it off, as you suggested.

You could add another rule making all target launch date equal to planned completion date to have a white background?

If this helped you, please mark correct to help others : )

View solution in original post

3 Replies

Avatar

Community Advisor

Column rules can be tricky. Did this start as multiple rules? If so, try reordering them. Otherwise, try in the standard mode of the rule to put in 'less than' (not less than equal, as you said) and then see what the text mode is for that to be sure it's correctly pulling less than, not less than equal. Sometimes when needing to build something in text mode I take it as far as standard mode allows, then convert it to text mode and revise from there.

If this helped you, please mark correct to help others : )

Avatar

Level 1

Hi Madalyn - Yes I used the advanced option to go as far as I could.  There is only the one rule on the target launch date column and I've confirmed that even in text mode the comparison operator is less than.

Avatar

Correct answer by
Community Advisor

I wonder if the time of day is throwing it off, as you suggested.

You could add another rule making all target launch date equal to planned completion date to have a white background?

If this helped you, please mark correct to help others : )