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

Solved
1222 1
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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?

1 Solution

Accepted Solutions
18 - Pluto

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
)
)``````
5 Replies 5
18 - Pluto

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
)
)``````
5 - Automation Enthusiast

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.

6 - Interface Innovator

I know this is an old post, but it is so close to what I need. My formula with this problem is:

IF(
“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(