The reason that the output is formatting as a string is because you’re using an empty string as your fallback value at the end of the innermost IF() statement. Even though the values you’re manually providing/calculating are numbers, apparently Airtable sees that string and assumes you want the output formatted as a string. Just omit that string (and the comma before it) and it will output a number as you wish.
I also believe you can simplify your formula a bit, only making the Stage="Closed Won" check once before doing the others. Tell me if this gives you the same result:
IF(
Stage="Closed Won",
IF(
(DATETIME_DIFF({Created Date},{Opportunity Close Date},'days')-1)<=0
,1
,DATETIME_DIFF({Created Date},{Opportunity Close Date},'days')*-1
)
)
The reason that the output is formatting as a string is because you’re using an empty string as your fallback value at the end of the innermost IF() statement. Even though the values you’re manually providing/calculating are numbers, apparently Airtable sees that string and assumes you want the output formatted as a string. Just omit that string (and the comma before it) and it will output a number as you wish.
I also believe you can simplify your formula a bit, only making the Stage="Closed Won" check once before doing the others. Tell me if this gives you the same result:
IF(
Stage="Closed Won",
IF(
(DATETIME_DIFF({Created Date},{Opportunity Close Date},'days')-1)<=0
,1
,DATETIME_DIFF({Created Date},{Opportunity Close Date},'days')*-1
)
)
Thanks @Justin_Barrett.
This worked. (Only one small correction. There needed to be a “*” before the first “-1”. My mistake when pasting the original formula)
Thank you for your help.
I know this is an old post, but it is so close to what I need. My formula with this problem is:
IF(
{Look Links}=0,
“0”,
IF({Sub-Type}=“dip powder”,
LEN(ARRAYJOIN({Look Links})) - LEN(SUBSTITUTE((ARRAYJOIN({Look Links})),’,’ , ‘’)),
LEN(ARRAYJOIN({Look Links})) - LEN(SUBSTITUTE((ARRAYJOIN({Look Links})),’,’ , ‘’))+1
)
)
I know this is an old post, but it is so close to what I need. My formula with this problem is:
IF(
{Look Links}=0,
“0”,
IF({Sub-Type}=“dip powder”,
LEN(ARRAYJOIN({Look Links})) - LEN(SUBSTITUTE((ARRAYJOIN({Look Links})),’,’ , ‘’)),
LEN(ARRAYJOIN({Look Links})) - LEN(SUBSTITUTE((ARRAYJOIN({Look Links})),’,’ , ‘’))+1
)
)
@Shayne_Bundy In your case, the non-numeric output is happening because of the “0” string in the third line. Change that to just a zero with no quotes around it, and the formula will output a number that you can use elsewhere.
However, that’s not the only problem. That first comparison isn’t the best way to check for an empty field. A more streamlined way that works with virtually all field types is to just use the field reference on its own.
IF({Field Reference}, value_if_true, optional_value_if_false)
If it contains anything, it will equate to True; otherwise it will equate to False. (I say “virtually” because number fields are problematic, with 0 also equating to False even though that number makes the field non-empty. The solution: concatenate the field contents with a string.)
In your case, you want to see if {Look Links} is empty, so you want to invert the normal test using NOT():
IF(NOT({Look Links}), 0, ...
One final optimization is a little trickier. Keeping in mind that the True and False results of conditions equate to 1 and 0 in most cases, you can calculate the number of items only once, and only add 1 to the count when {Sub-Type} is not “dip powder”.
The final optimized version of the formula is:
IF(
NOT({Look Links}),
0,
LEN(ARRAYJOIN({Look Links})) - LEN(SUBSTITUTE(ARRAYJOIN({Look Links}), ",", ""))+({Sub-Type} != "dip powder")
)
@Shayne_Bundy In your case, the non-numeric output is happening because of the “0” string in the third line. Change that to just a zero with no quotes around it, and the formula will output a number that you can use elsewhere.
However, that’s not the only problem. That first comparison isn’t the best way to check for an empty field. A more streamlined way that works with virtually all field types is to just use the field reference on its own.
IF({Field Reference}, value_if_true, optional_value_if_false)
If it contains anything, it will equate to True; otherwise it will equate to False. (I say “virtually” because number fields are problematic, with 0 also equating to False even though that number makes the field non-empty. The solution: concatenate the field contents with a string.)
In your case, you want to see if {Look Links} is empty, so you want to invert the normal test using NOT():
IF(NOT({Look Links}), 0, ...
One final optimization is a little trickier. Keeping in mind that the True and False results of conditions equate to 1 and 0 in most cases, you can calculate the number of items only once, and only add 1 to the count when {Sub-Type} is not “dip powder”.
The final optimized version of the formula is:
IF(
NOT({Look Links}),
0,
LEN(ARRAYJOIN({Look Links})) - LEN(SUBSTITUTE(ARRAYJOIN({Look Links}), ",", ""))+({Sub-Type} != "dip powder")
)
Thank you for this clean-up! I found out the “0” vs. 0 thing on my own, but I am absolutely terrible at making streamlined, optimized formulae. I appreciate the help!!
