Less than function when comparing two dates - Planned Completion date and a custom date does not work | Community
Skip to main content
J
JenniferTo4
April 14, 2023
Solved

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

  • April 14, 2023
  • 2 replies
  • 805 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Madalyn_Destafney

    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?

    2 replies

    Madalyn_Destafney
    Community Advisor
    Madalyn_DestafneyCommunity Advisor
    Community Advisor
    April 18, 2023

    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 : )
    J
    JenniferTo4Author
    April 18, 2023

    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.

    Madalyn_Destafney
    Community Advisor
    Madalyn_DestafneyCommunity AdvisorAccepted solution
    Community Advisor
    April 18, 2023

    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 : )
    Terms & ConditionsAccessibility statement

    Loading footer...