Adding a date rule to a formula

[Becky_Taylor]

I have the below formula, which I had support creating. I now need to add one additional rule. I would like to add that if todays date is after {Next Renewal Date (from X Customers)} then the cell should read STAGE 5 - Closed.

I have attempted this in the 2nd formulae below and it works but it is also putting ‘Stage 5 - Closed’ against all the dates that are in the future too, which is wrong as todays date is not after these dates in the future. Hopefully this makes sense, help would be greatly appreciated as the guy that normally helps me is OoO now for 2 weeks! Thanks!

Original formula:

IF(
AND(TODAY() < {105 - Stage 1}, {120 - Stage 0} <= TODAY()),
“STAGE 0 - New Renewal”,IF(AND({105 - Stage 1}<=TODAY(),TODAY()<{30 - Stage 2}),“STAGE 1 - Discovery”,IF(AND({30 - Stage 2}<=TODAY(),TODAY()<{14 - Stage 3}),“STAGE 2 - Proposal Sent”,IF(AND({14 - Stage 3}<=TODAY(),TODAY()<{7 - Stage 4}),“STAGE 3 - Forecast/Commit”,IF(AND({7 - Stage 4}<=TODAY(),TODAY()<{Next Renewal Date (from X Customers)}),“STAGE 4 - Order Processing”))))
)

My updated 2nd formula:

IF(
AND(TODAY() < {105 - Stage 1}, {120 - Stage 0} <= TODAY()),
“STAGE 0 - New Renewal”,IF(AND({105 - Stage 1}<=TODAY(),TODAY()<{30 - Stage 2}),“STAGE 1 - Discovery”,IF(AND({30 - Stage 2}<=TODAY(),TODAY()<{14 - Stage 3}),“STAGE 2 - Proposal Sent”,IF(AND({14 - Stage 3}<=TODAY(),TODAY()<{7 - Stage 4}),“STAGE 3 - Forecast/Commit”,IF(AND({7 - Stage 4}<=TODAY(),TODAY()<{Next Renewal Date (from X Customers)}),“STAGE 4 - Order Processing”,IF(TODAY() > {Next Renewal Date (from X Customers)},“STAGE 5 - Closed”)))))
)

[Kris]

Hi Becky,

Quite the beefy IF statement you have there =P

Not sure at a glance why it wouldn’t work, but what happens if you paste this in:

IF(AND(TODAY()<{105 - Stage 1},{120 - Stage 0}<=TODAY()),"STAGE 0 - New Renewal",IF(AND({105 - Stage 1}<=TODAY(),TODAY()<{30 - Stage 2}),"STAGE 1 - Discovery",IF(AND({30 - Stage 2}<=TODAY(),TODAY()<{14 - Stage 3}),"STAGE 2 - Proposal Sent",IF(AND({14 - Stage 3}<=TODAY(),TODAY()<{7 - Stage 4}),"STAGE 3 - Forecast/Commit",IF(AND({7 - Stage 4}<=TODAY(),TODAY()<{Next Renewal Date (from X Customers)}),"STAGE 4 - Order Processing", IF(AND({7 - Stage 4}<=TODAY(),TODAY()<{Next Renewal Date (from X Customers)}),"STAGE 4 - Order Processing", IF(TODAY()>{Next Renewal Date (from X Customers)},"STAGE 5 - Closed", "Not Closed?")))))))

Curious if the second value is needed in the final IF statement. Also rebuilt in case there was a syntax error with quotes or a bracket out of place.

[Becky_Taylor]

Thank you!

Sadly this hasn’t worked, it’s still putting “STAGE 5 - Closed” against contracts who’s renewal dates are, for example, 1 Jan 2021, 14 Feb 2021, 15 May 2021 etc., as well as the correct ones, for example, 1 Sep 20, 26 Aug 20 … any thoughts, or any more info I can provide?

[Kris]

So based on what you’re saying is happening – the “Next Renewal Date (from X Customers)” is evaluating as BEFORE today. I’m wondering if perhaps there is a formatting issue on that specific field.

If you create a separate field just for testing and use only this formula:

IF(TODAY()>{Next Renewal Date (from X Customers)},"TRUE", "FALSE")

Does it output TRUE/FALSE in the places it should? You could try a DATETIME_PARSE on your field and see if that changes it (in case it’s a string and not datetime object):

IF(TODAY()>DATETIME_PARSE({Next Renewal Date (from X Customers)}, 'D MMM YYYY'),"TRUE", "FALSE")

[Becky_Taylor]

So both of those are returning True for every field! :frowning: !

I’ve attached an image if remotely helpful to get an idea of what the sheet looks like. The ‘Next Renewal Date’ is a straightforward date pulled from the ‘Master Customer’ sheet, so shouldn’t be causing issues (which I think is what you thought might be happening?).

Thanks!

[Kris]

Hey @Becky_Taylor,

Just noticed your reply here. So after seeing that screenshot, I’m thinking we’re on the right track. Does the lookup field for ‘Next Renewal Date’ have any formatting applied? I’m thinking it may be coming as a string, and then the DATETIME_PARSE I gave you was based on the ‘Jan/Feb/ect.’ 3 letter month. For the full month name like it is in your screenshot, you would use:

IF(TODAY()>DATETIME_PARSE({Next Renewal Date (from Mux Customers)}, 'D MMMM YYYY'),"TRUE", "FALSE")

With ‘MMMM’ instead of ‘MMM.’ (Sounds like a tasty solution… :stuck_out_tongue: )

[Becky_Taylor]

Hi Kris,

Ok so here is a screenshot of the formatting on the ‘Next Renewal Date’ field.

And here is what happens when I do your new formula in the Test column…getting True for everything…? :woman_facepalming:t2:

On another note, I would actually much prefer the date to be 3 letters rather than the full word, is this easy to change? I thought it would be an automatic option from AirTable in the dropdown but there is only the full word option!

Thanks!