What if/then formula can I use for status color?

edited 05/21/20 in Formulas and Functions


I need an if/then formula to automatically change the Status symbol to green if the date in the Batch Received column is within 7 days. And automatically change the Status symbol to red if the date is past 7 days.

See example below:

Thanks in advance!


  • Satyadev Kolli
    edited 05/21/20

    In the Status1 column you can try this formula:

    =IF(([Batch Received]1 - TODAY()) - 7 > 0, "Green", "Red")

    In your question, it is not clear if the "Batch Received" is a due date, i.e., are you counting as you are coming up to this data or past this date. So, formula may have to adjusted accordingly. Further, the formula is looking for within 7 days on either side of "batch received" date.

  • nmazardw

    Thank you, =IF(([Batch Received]1 - TODAY()) + 7 > 0, "Green", "Red") worked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!