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?
Best answer by Justin_Barrett
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
)
)
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
)
)
@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 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”.
@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 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”.
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!!
