Nested IF() Output Won't Convert to Number with Value()

[Randy_Shoemaker]

Here’s my code:
IF(AND(Stage=“Closed Won”,(DATETIME_DIFF({Created Date / Time},{Opportunity Close Date},‘days’)-1)<=0),1,IF(AND(Stage=“Closed Won”,(DATETIME_DIFF({Created Date / Time},{Opportunity Close Date},‘days’)-1)>0),DATETIME_DIFF({Created Date / Time},{Opportunity Close Date},‘days’)*-1,""))

The output is a number but in a text format (I can’t sum, avg, etc) and it’s formatting to the left of the column. I put Value() around the whole thing and I’m getting errors in the records when doing that.

Created date: Date the record was created
Opportunity Close Date: Date the record was marked closed won

I want to be able to sum and avg etc the column.

Any help?

[Justin_Barrett · Accepted solution]

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

[Randy_Shoemaker]

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.

[Shayne_Bundy]

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

[Justin_Barrett]

@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]

Justin_Barrett:

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