Help needed for a formulae that changes the status symbol when a specific sale stage is >30days
Further information:
I'd appreciate some support as I cannot work out the formulae to give the following:
Stage 1 - 2 is Blue when <30days old and Red when <30days old
Stage 3 - 5 is Green when <30days old and RED when old
Note: Sales Stage Change Date is a automatically populated when Sale Stage value changes.
Answers
-
Are you able to clarify? For stages 1-2 you have red and blue being populated when less than 30 days old and for stages 3-5 you have less than 30 days old is green, but then you just say "old" for red.
-
sorry, typo in the original config:
Stage 1 - 2 is Blue when <=30days old and Red when >30days
Stage 3 - 5 is Green when <=30days old and RED when >30days
-
Try something like the below. It is a slight restructure of the logic, but should still grab everything.
=IF([Sales Stage Change Date]@row< TODAY(-30), "Red", IF(VALUE(MID([Sales Stage]@row, 7, 1))<= 2, "Blue", "Green"))
It looks to me like regardless of stage it should be "Red" when more than 30 days old, so we do not need to specify anything more than the date criteria. With nested IFs, it will work from left to right and stop on the first true value. We can leverage this to assume that if it makes it past the first one then the first one must be false.
This means anything outside of the "Red" criteria will be less than or equal to 30 days old, so at that point all we need to do is differentiate between the stages for "Green" vs "Blue". To do this we pull the number out using the VALUE/MID combo and say that if it is less than or equal to 2 (meaning 1 or 2) then output "Blue".
Going back to the nested IF logic... If it makes it past the first then it must be less than 30 days old, and if it makes it past the second then it must be both less than 30 days old and in a stage greater than 2. Then means all we have to do is assign the "Green" to "everything else" without having to worry about specifying any kind of criteria.
-
Hi Paul
Thank you so much for explaining so well and providing the formulae.
I can confirm it provided the required outcome - thank you!
-
Happy to help. 👍️
-
Hi, I had to change the sales stage options to the below list and the formulae nolonger works "INVALID VALUE" despite the stage numbering remaining.
Stage 1: (identifying opp)
Stage 2: (qualifying opp)
Stage 3: (developing proposal)
Stage 4: (delivering proposal)
Stage 5: (finalising deal)
-
Hi @Lee Rudd
Do the rows you're testing have a date value in the other column?
Can you post a screen capture of how you updated the Stages? The MID function is looking for the seventh character, so if there's an extra space or no space between "Stage" and the number you'll see an error.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Thank you, I have updated the MID function character number to reflect the changein stage names made. This has resolved the issue.
THANK YOU!!!!
-
Glad to hear it! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 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!