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

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.

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.

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

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.

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

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
    )
  )
)

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!

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?

Did you replace the greater than symbols with less than symbols?

In general, when nesting if statements for ranges, start with the smallest number, and always use less than < symbols (or <=).

Ah, the result you are seeing is because DATETIME_DIFF subtracts the second date from the first date. If the first date is later, you have a negative result.

From the formula field reference:

The difference between datetimes is determined by subtracting [date2] from [date1]. This means that if [date2] is later than [date1], the resulting value will be negative.

Swap the order of {Last Contact} and TODAY() to get positive numbers.

Debugging formulas often works like this–fixing one error only to discover another one. Eventually you’ll get there.

Great! I got exactly what I wanted, and I learnt something too!