Auto-status, 'Up Next' criteria overlap

Emma Schachter
Emma Schachter ✭✭✭
edited 04/30/20 in Formulas and Functions

Hello! I am working on a sheet with Start/Finish dates and need to populate status' into the following 4 categories: not started, in progress, complete, up next

For not started/in progress/complete i have the following formula working:

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

For the 'up next' i have the following working:

=IF([Start Date]@row > TODAY(30), ([Finish Date]@row), "Up Next")

I can not get them to work together - it returns an #INCORRECT ARGUMENT SET error. While there is seemingly overlap, anything that is ‘not started’ but starting within 30 days should be considered ‘up next’ which should disqualify it from 'not started' and recategorize it accordingly.

Any help would be much appreciated!!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!