Hello Airtablers!
For my CRM I’m trying to automate the ranking of my prospects from “ 
Flaming hot” to " 
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!
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!
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.
Wow, plenty of simple errors I hadn’t even noticed!
Thank you, will give it a go and report back
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?
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.
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?
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
)
)
)
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
)
)
)
@kuovonne - I do that by inserting the line by two spaces, right?
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?
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 <=).
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.
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!
Great! I got exactly what I wanted, 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.
