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


#1

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?


#2

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

#3

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.