Change Symbols with respect to due dates

UKASHA
UKASHA ✭✭
edited 07/12/22 in Formulas and Functions

I want to do this

if Due date has 3 or less than 3 days left ="Yellow"

if Due date has passed ="Red"

if Due date has more than 3 days left ="Red"

if Due date row is empty = "Gray"

The formula I'm using

=IF([Due Date]@row = TODAY(3), "Yellow", IF([Due Date]@row = TODAY(2), "Yellow", IF([Due Date]@row = TODAY(1), "Yellow", IF([Due Date]@row = TODAY(0), "Yellow", IF(AND([Due Date]@row < TODAY(0), [Due Date]@row > 0), "Red", IF([Due Date]@row > TODAY(3), "Green",IF([Due Date]@row = 0, "Gray")))))))

Best Answers

  • UKASHA
    UKASHA ✭✭
    Answer ✓

    I got the solution it was just a mistake in order of if statements

  • David G
    David G ✭✭
    Answer ✓

    Hi @UKASHA

    Can you provide the correct formula? 😎

  • UKASHA
    UKASHA ✭✭
    Answer ✓

    Hi @David G Correct formula is here

    if Due date has 3 or less than 3 days left ="Yellow"

    if Due date has passed ="Red"

    if Due date has more than 3 days left ="Green"

    if Due date row is empty = "Gray"


    =(IF(ISBLANK([Due Date]@row), "Gray", IF([Due Date]@row = TODAY(3), "Yellow", IF([Due Date]@row = TODAY(2), "Yellow", IF([Due Date]@row = TODAY(1), "Yellow", IF([Due Date]@row = TODAY(0), "Yellow", IF([Due Date]@row > TODAY(3), "Green", IF([Due Date]@row < TODAY(), "Red"))))))))

Answers

  • UKASHA
    UKASHA ✭✭
    Answer ✓

    I got the solution it was just a mistake in order of if statements

  • David G
    David G ✭✭
    Answer ✓

    Hi @UKASHA

    Can you provide the correct formula? 😎

  • UKASHA
    UKASHA ✭✭
    Answer ✓

    Hi @David G Correct formula is here

    if Due date has 3 or less than 3 days left ="Yellow"

    if Due date has passed ="Red"

    if Due date has more than 3 days left ="Green"

    if Due date row is empty = "Gray"


    =(IF(ISBLANK([Due Date]@row), "Gray", IF([Due Date]@row = TODAY(3), "Yellow", IF([Due Date]@row = TODAY(2), "Yellow", IF([Due Date]@row = TODAY(1), "Yellow", IF([Due Date]@row = TODAY(0), "Yellow", IF([Due Date]@row > TODAY(3), "Green", IF([Due Date]@row < TODAY(), "Red"))))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!