Help

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

Solved
Jump to Solution
1966 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Edd_Turner
6 - Interface Innovator
6 - Interface Innovator

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!

14 Replies 14

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

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

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!

Bildschirmfoto 2020-04-09 um 20.47.31

Sometimes that’s the best part!

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