Discussions

Expand all | Collapse all

Row colorization base on date (current, a day before, and past due)

  • 1.  Row colorization base on date (current, a day before, and past due)

    Posted 08-04-2017 04:36
    I am trying to create the following colorization based on a date field:
    1 day prior to [ETA] & [Arrival Date] is empty = yellow
    [ETA] = today's date & [Arrival Date] is empty = green
    [ETA] date has passed & [Arrival Date] is empty = red

    Thank you!


  • 2.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-04-2017 14:02
    I think this will work in the colorization formula:

    If([Arrival Date]=��,Case([ETA],1=ToDays([ETA]-Today()),�#ffff00�,[ETA]=Today(),�#00ff00�,ToDays([ETA]-Today())<0,�#ff0000�),��)


  • 3.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-04-2017 14:04


  • 4.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-04-2017 17:47
    Here is the error I get:
    Formula syntax error
    Expected a valid expression after the "="
    If([Arrival Date]=  ��,Case([ETA],1=ToDays([ETA]-Today()),�#ffff00�,[ETA]=Today(),�#00ff00�,ToDays([ETA]-Today())<0,�#ff0000�),��)


  • 5.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-04-2017 20:36
    Try .....[Arrival Date]=ToDate(""),.....


  • 6.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-05-2017 02:02
    This is what I got:

    Formula syntax error

    Please check the syntax of your entire formula. Possible culprits are a mismatched set of parentheses, missing quotes, or an extra bracket.

    If([Arrival Date]=ToDate(""),Case([ETA],1=ToDays([ETA]-Today()),  �#ffff00�,[ETA]=Today(),�#00ff00�,ToDays([ETA]-Today())<0,�#ff0000�),��)


  • 7.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-05-2017 02:08
    try this

    If(
    not Isnull([Arrival Date]), "",
    ToDays([ETA]-Today()) = 1,  "#ffff00",
    [ETA]=Today(),"#00ff00",
    ToDays([ETA]-Today())<0,"#ff0000")

    be sure you are using the straight double quotes and not the "66" "99" quotes which you see in Word.


  • 8.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-05-2017 02:17
    I did change the quotes, because I thought that was it, but this formula worked. Thank you!!


  • 9.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-05-2017 02:31
    Actually, had to change [ETA]=Today() to ToDays([ETA]-Today())>0, but I have what I need. Thanks again!


  • 10.  RE: Row colorization base on date (current, a day before, and past due)

    Posted 08-05-2017 02:37
    OK great, I did not have the time to check your logic, only your syntax.