Nested formula and DATETIME_DIFF not turning out, where is my simple error?

[Edd_Turner]

Hello Airtablers!

For my CRM I’m trying to automate the ranking of my prospects from “ :fire: Flaming hot” to " :skull: Dead".

I tried using a SWITCH formula but got in difficulties, so I reverted to IF and DATETIME_DIFF.

I want to switch between the 6 values below and though I managed an IF statement with just 2 values, when I try 6 (and DATETIME_DIFF) I get no-where (I even checked for curly brackets, straight quote marks and the number or parentheses)!

Can you help?

I tried the following:

IF({Last Contact}=TODAY(),"🔥 Hot!",

IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>3,"🌡 Warm",

IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>7,"😎 Chilled",

IF((DATETIME_DIFF{Last Contact},TODAY(),'days')>21,"🚰 Tepid",

IF((DATETIME_DIFF{Last Contact},TODAY(),'days')<90,"🥶 Cold",

"💀 Dead!")

)

)

)

)

Thanks in advance!

[AlliAlosa · Accepted solution]

Hi there!

Looks like you’ve got some parentheses misplaced. For each time you’ve written…

IF((DATETIME_DIFF{Last Contact},TODAY()...

There is an extra parentheses after “IF”, and you’re missing one before “{Last Contact}”:

IF(DATETIME_DIFF({Last Contact},TODAY()...

The entire formula should look like…

IF({Last Contact}=TODAY(),"🔥 Hot!",

IF(DATETIME_DIFF({Last Contact},TODAY(),'days')>3,"🌡 Warm",

IF(DATETIME_DIFF({Last Contact},TODAY(),'days')>7,"😎 Chilled",

IF(DATETIME_DIFF({Last Contact},TODAY(),'days')>21,"🚰 Tepid",

IF(DATETIME_DIFF({Last Contact},TODAY(),'days')<90,"🥶 Cold",

"💀 Dead!")

)

)

)

)

Hope that helps!

[kuovonne]

It also looks like you are using greater than signs instead of less than signs.

Most of your rankings aren’t reached because the formula stops evaluating when it finds the diff greater than 3.

Try using all less than signs and a final “else” condition.

[Edd_Turner]

Wow, plenty of simple errors I hadn’t even noticed!

Thank you, will give it a go and report back

[Edd_Turner]

My Google fu isn’t helping me find a cheatsheet for ‘else’ - what does it look like?

[kuovonne]

You’re actually already using the else condition. It’s the third parameter in the IF function. You can find the syntax in the formula field reference.

[Edd_Turner]

Do you mean the last bit - return skull if none of the above?

[kuovonne]

Yes, the skull is the final else parameter.

Technically, all of your nested if statements are also “else” conditions.
That is why I personally like to format nested if statements the following way:

IF(condition1,
  result1,
  IF(condition2,
    result2,
    IF(condition3,
      result3,
      result4
    )
  )
)

[Edd_Turner]

@kuovonne - I do that by inserting the line by two spaces, right?

[Edd_Turner]

Oh dear, oh dear - now everything (even the really old contacts) are either ‘hot’ or ‘warm’ - no cool, tepid or zombies.

Should I put the list in order from largest to smallest? Does that help?