Overall response status is getting removed...

I am trying to provide a status on our department response time. We have 30 days to respond to another department for requests, once they are received. 

1)     I wanted to put a Green, Yellow, and Red status on the request, based on if the open request is:

a.     greater than 14 days until the due date, (Green)

b.     greater than 7 days until the due date, (Yellow)

c.     less than 7 days until the due date, (Red)

2)     I also wanted to make sure that if we met the 30-day requirement, the overall status for the request is green or if we were late in the response the overall request status is Red.

3)     Here is my Logic:

a.     =IF([Actual Response Date]@row = "", IF(TODAY() + 14 < [Response Due Date]@row, "Green", IF(TODAY() + 7 < [Response Due Date]@row, "Yellow", IF([Response Due Date]@row > [Actual Response Date]@row, "Green", "Red"))))

Most of the logic is working but once a valid date if loaded in the Actual Request date the logic does not return a result. Does anyone see what I am missing?

Best Answers

  • Michael Mather
    Answer ✓

    Hello Andree,

    Thank you for following up. I think I figured it, I used the ISBLANK() function to split out the two conditions to apply the logic. This is what I came up with:

    =IF(ISBLANK([Actual Pricing Response Date]@row), IF(TODAY() + 14 < [Pricing Response Due Date]@row, "Green", IF(TODAY() + 7 < [Pricing Response Due Date]@row, "Yellow", "Red")), IF([Actual Pricing Response Date]@row < [Pricing Response Due Date]@row, "Green", "Red")) it does seem to be working. Thanks.

    Mike Mather

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 04/19/21 Answer ✓

    @Michael Mather

    Excellent!

    You're more than welcome!

    Happy that you figured it out!

    Here's a another version of the same formula.

    =IF([Actual Pricing Response Date]@row="", IF(TODAY(14) < [Pricing Response Due Date]@row, "Green", IF(TODAY(7) < [Pricing Response Due Date]@row, "Yellow", "Red")), IF([Actual Pricing Response Date]@row < [Pricing Response Due Date]@row, "Green", "Red")

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

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

    Hi @Michael Mather

    I hope you're well and safe!

    I don't see an Actual Request date in the formula?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Michael Mather
    Answer ✓

    Hello Andree,

    Thank you for following up. I think I figured it, I used the ISBLANK() function to split out the two conditions to apply the logic. This is what I came up with:

    =IF(ISBLANK([Actual Pricing Response Date]@row), IF(TODAY() + 14 < [Pricing Response Due Date]@row, "Green", IF(TODAY() + 7 < [Pricing Response Due Date]@row, "Yellow", "Red")), IF([Actual Pricing Response Date]@row < [Pricing Response Due Date]@row, "Green", "Red")) it does seem to be working. Thanks.

    Mike Mather

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 04/19/21 Answer ✓

    @Michael Mather

    Excellent!

    You're more than welcome!

    Happy that you figured it out!

    Here's a another version of the same formula.

    =IF([Actual Pricing Response Date]@row="", IF(TODAY(14) < [Pricing Response Due Date]@row, "Green", IF(TODAY(7) < [Pricing Response Due Date]@row, "Yellow", "Red")), IF([Actual Pricing Response Date]@row < [Pricing Response Due Date]@row, "Green", "Red")

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!