Help needed for a formulae that changes the status symbol when a specific sale stage is >30days

Lee Rudd

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.

Paul Newcome

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.

Lee Rudd

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

Paul Newcome

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.

Lee Rudd

Hi Paul

Thank you so much for explaining so well and providing the formulae.

I can confirm it provided the required outcome - thank you!

Paul Newcome

Happy to help. 👍️

Lee Rudd

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)

Genevieve P.

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.

Lee Rudd

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

Genevieve P.

Glad to hear it! 🙂