Auto-status, 'Up Next' criteria overlap
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!!
Answers
-
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")))
-
@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?
-
All 4 statuses are built in to the formula. Which one is not being triggered?
-
@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!
-
To clarify - i need a separate column that represents the status' with the RYGB dots.
-
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")))
-
@Paul Newcome i got it! thank you so much!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!