Skip to main content

Is there a way for a formula to appear only on certain conditions? For example, I have the following situation:



  • I have a # field and a date field

  • I’d like to create a formula that auto calculates three-weeks out from my date field ONLY when the # field is not empty


Here is the formula that I currently have:

DATETIME_FORMAT(DATEADD({Clinic Date}, 3, ‘week’),“MM-DD-YYYY”)


Is there a way to set up that additional condition so I don’t further clutter up my database?


Thanks in advance to anyone who can assist!

You can check if a cell is blank by simply referencing the cell you want to check in the logic part of the IF statement. Here is the formula I would use:


IF({Number},DATETIME_FORMAT(DATEADD({Clinic Date},3,'week'),'MM-DD-YYYY'),BLANK())

While @Joseph_Abraham is correct, there are a couple of other things to point out:



  1. If you want the field to remain empty if the condition in an IF() function is false, you don’t need to use BLANK() as the final argument. You can leave that off and Airtable will automatically leave the field empty.

  2. When checking a number field, a 0 (zero) in that field will also be treated as equivalent to false when using the field-name-only test that was described. In those cases, a suggested workaround is to concatenate the number field output with an empty string, which will force the result to a string. In that case “0” is a non-empty string, which is equivalent to true. A truly empty number field concatenated with an empty string will leave an empty string, which is equivalent to false.


With those concepts applied, the formula becomes this:


IF({Number} & "",DATETIME_FORMAT(DATEADD({Clinic Date}, 3, 'week'), 'MM-DD-YYYY'))

While @Joseph_Abraham is correct, there are a couple of other things to point out:



  1. If you want the field to remain empty if the condition in an IF() function is false, you don’t need to use BLANK() as the final argument. You can leave that off and Airtable will automatically leave the field empty.

  2. When checking a number field, a 0 (zero) in that field will also be treated as equivalent to false when using the field-name-only test that was described. In those cases, a suggested workaround is to concatenate the number field output with an empty string, which will force the result to a string. In that case “0” is a non-empty string, which is equivalent to true. A truly empty number field concatenated with an empty string will leave an empty string, which is equivalent to false.


With those concepts applied, the formula becomes this:


IF({Number} & "",DATETIME_FORMAT(DATEADD({Clinic Date}, 3, 'week'), 'MM-DD-YYYY'))

Thanks to you both! This worked for me!

While @Joseph_Abraham is correct, there are a couple of other things to point out:



  1. If you want the field to remain empty if the condition in an IF() function is false, you don’t need to use BLANK() as the final argument. You can leave that off and Airtable will automatically leave the field empty.

  2. When checking a number field, a 0 (zero) in that field will also be treated as equivalent to false when using the field-name-only test that was described. In those cases, a suggested workaround is to concatenate the number field output with an empty string, which will force the result to a string. In that case “0” is a non-empty string, which is equivalent to true. A truly empty number field concatenated with an empty string will leave an empty string, which is equivalent to false.


With those concepts applied, the formula becomes this:


IF({Number} & "",DATETIME_FORMAT(DATEADD({Clinic Date}, 3, 'week'), 'MM-DD-YYYY'))

Thanks @Justin_Barrett! TIL that a zero will also be treated as false when using the field-name-only test. I will keep your workaround in mind next time!

Reply


Terms & Conditions