Changing column Property Status (Red, Yellow, Green) when two columns dates are different

Roberto SanchezRoberto Sanchez ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
07/13/19 Edited 12/09/19

I am in need for the status colum 1 (red, yellow, green) to automatically change when column 2 (date) is passed column 3 (date). Can this be done with a formula? 

I can do "=IF([Approved Required Delivered Date]1 < [Adjusted Delivery Date]1, "Red")", however, I am sure on how to add the other two conditions in the formula.

Thank you,

 

Comments

  • Hi Roberto,

    try this:

    =IF([Date1]@row < [Date2]@row; "Red"; IF([Date1]@row = [Date2]@row; "Yellow"; IF([Date1]@row > [Date2]@row; "Green")))

    Regards

    Andreas

     

  • Roberto SanchezRoberto Sanchez ✭✭✭✭✭

    Andreas, I tried your recommendation; however, it is coming up with a #UNPARSEABLE message. This is what I entered:

     

    =IF([Approved Required Delivered Date]1<[Adjusted Delivery Date]1,"Red"; IF([Approved Required Delivered Date]1>[Adjusted Delivery Date]1,"Green"; IF([Approved Required Delivered Date]1>[Adjusted Delivery Date]1, "Green")))

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 07/15/19

    Hi Roberto,

    Try this. 

    =IF([Approved Required Delivered Date]1<[Adjusted Delivery Date]1,"Red", IF([Approved Required Delivered Date]1>[Adjusted Delivery Date]1,"Green",IF([Approved Required Delivered Date]1>[Adjusted Delivery Date]1, "Green")))

    Did it work?

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Roberto SanchezRoberto Sanchez ✭✭✭✭✭

    Thank you. It worked. 

  • Does this mean different languages have to enter Formulars different? 

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Excellent!

    Happy to help!

    The reason it didn't work was because you mixed commas and semicolons. It's different depending on the region. 

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Yes, that's correct! Different regions use different characters. 

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • That's a nightmare. 

    What when I share a sheet to a different region? 

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    When you share with another region, they will see it in their format and can add/update with their format, so it's not that big of a deal.

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.