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

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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!