Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Formula Conditions

Solved
Jump to Solution
1634 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara_Stahlberg
6 - Interface Innovator
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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

See Solution in Thread

4 Replies 4
Joseph_Abraham
5 - Automation Enthusiast
5 - Automation Enthusiast

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())
Justin_Barrett
18 - Pluto
18 - Pluto

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!

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!