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

Edd_Turner · ‎Apr 09, 2020

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 · ‎Apr 09, 2020

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 · ‎Apr 09, 2020

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 · ‎Apr 09, 2020

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 · ‎Apr 09, 2020

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

kuovonne · ‎Apr 09, 2020

Sometimes that’s the best part!

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