Formula for Status change based on dates/ blank cells/ TBD

06/28/19 Edited 12/09/19

Hello all, 

I really found this community helpful so far in learning how to use Smartsheets. I just wanted to provide a formula I created in case it is helpful for anyone else. I didn't find the exact info I needed when trying to make it, but I built this with the help of many discussion comments.

 

This formula will calculate (Based on Start Date/ End Date) if the task is "Not Started", "In Progress", "Complete". It will also leave the cell blank if the date cells are blank, and it will change the status to TBD if the end date is TBD. 

 

Note: You will need to be sure the row line matches the formula (this is copied from row 14). 

=IF(AND(ISBLANK([Start Date]14), ISBLANK([End Date]14)), "", IF([End Date]14 = "TBD", "TBD", IF(AND([End Date]14 > TODAY(), [Start Date]14 < TODAY()), "In Progress", IF(AND([End Date]14 > TODAY(), [Start Date]14 > TODAY()), "Not Started", IF(AND([End Date]14 < TODAY(), [Start Date]14 < TODAY()), "Complete", "")))))

 

Good luck with all your projects!

Comments

  • lmiddletonlmiddleton ✭✭✭✭✭

    As a helpful hint for the future, use "@row" in your formulas instead of the row number. This improves the performance considerably and helps prevent the formula from breaking if any rows are added. 

    e.g. 

    =IF(AND(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF([End Date]@row = "TBD", "TBD", IF(AND([End Date]@row > TODAY(), [Start Date]@row < TODAY()), "In Progress", IF(AND([End Date]@row > TODAY(), [Start Date]@row > TODAY()), "Not Started", IF(AND([End Date]@row < TODAY(), [Start Date]@row < TODAY()), "Complete", "")))))

     

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

    Hi Kristie,

    Thanks for sharing!

    Have a fantastic Weekend!

    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.

  • Thanks for the tip!

  • Some more changes I made:

     

    -Row numbers changed to @row (Thanks imiddleton)

    -If the task starts today it says "In progress", was showing blank

    -If the task ends today it says "Today", was showing blank

     

     

    =IF(AND(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF(OR([End Date]@row = "TBD", [Start Date]@row = "TBD"), "TBD", IF(OR(AND([End Date]@row > TODAY(), [Start Date]@row < TODAY()), AND([Start Date]@row = TODAY(), [End Date]@row > TODAY())), "In Progress", IF(AND([End Date]@row > TODAY(), [Start Date]@row > TODAY()), "Not Started", IF(AND([End Date]@row < TODAY(), [Start Date]@row < TODAY()), "Complete", IF([End Date]@row = TODAY(), "TODAY", ""))))))

Sign In or Register to comment.