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

Kristie.Bredfeldt

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!

lmiddleton

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å

Hi Kristie,

Thanks for sharing!

Have a fantastic Weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Kristie.Bredfeldt

Thanks for the tip!

Kristie.Bredfeldt

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