Auto-status, 'Up Next' criteria overlap

Emma Schachter

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!!

Paul Newcome

Try this...

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

Emma Schachter

@Paul Newcome that formula works, but it only leaves me with 3 status'. i need to have the 4 status'. is there a way to do that?

Paul Newcome

All 4 statuses are built in to the formula. Which one is not being triggered?

Emma Schachter

@Paul Newcome ah! ok so the written status' work, but how do i alter it to have the colors? id like them to be:

yellow - in progress

red - not started

blue - up next

green - complete

thank you so much!

Emma Schachter

To clarify - i need a separate column that represents the status' with the RYGB dots.

Paul Newcome

Just replace the text and place it in a rygb type of column.

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

Changes to

=IF([Finish Date]@row < TODAY(), "Green", IF([Start Date]@row > TODAY(30), "Red", IF([Start Date]@row > TODAY(), "Blue", "Yellow")))

Emma Schachter

@Paul Newcome i got it! thank you so much!

Paul Newcome

Happy to help! 👍️