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

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!

• 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", "")))))

• ✭✭✭✭✭✭

Hi Kristie,

Thanks for sharing!

Have a fantastic Weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

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.

• 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", ""))))))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!