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!

[kuovonne]

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

[Edd_Turner]

Looks like this:

  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",
             IF(DATETIME_DIFF({Last Contact},TODAY(),'days')<120,
              "🧟‍♂️ Zombie", "💀 Dead!"
              )
             )
            )
           )
          )
         )

[kuovonne]

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.

[Edd_Turner]

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

[kuovonne]

Edd_Turner:

and I learnt something too!

Sometimes that’s the best part!

I hope that you feel empowered to write and troubleshoot more formulas after this.