- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 05, 2021 06:33 PM
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!
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 06, 2021 08:30 AM
While @Joseph_Abraham is correct, there are a couple of other things to point out:
- If you want the field to remain empty if the condition in an
IF()
function is false, you don’t need to useBLANK()
as the final argument. You can leave that off and Airtable will automatically leave the field empty. - 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'))
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 05, 2021 07:01 PM
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())
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 06, 2021 08:30 AM
While @Joseph_Abraham is correct, there are a couple of other things to point out:
- If you want the field to remain empty if the condition in an
IF()
function is false, you don’t need to useBLANK()
as the final argument. You can leave that off and Airtable will automatically leave the field empty. - 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'))
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 06, 2021 01:07 PM
Thanks to you both! This worked for me!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 06, 2021 02:43 PM
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!