Conditional Formatting formula?

Hi Tamara,

There is not currently conditional formatting available at the “cell” level. On a Pro plan you can use the “Color” menu to put a colored bar on records that meet conditions you set, as in this example:

Notice the colored bars down the left side of each record

There is another option that I often use as a workaround as well, though, which will work with free accounts – that is to use a formula field that outputs an eye-catching emoji when some condition is met. Here are a couple examples:

The formula:

IF(
   {Num. Of Samples} < {No. Of Samples},
   "🔴 Too Many Samples Added 🔴",
   IF(
      {No. Of Samples} < {Num. Of Samples},
      "🔴 Too Few Samples Added 🔴",
      "✅ Good ✅"
   )
)

The formula:

IF(
  {Missing?}=TRUE(),
  "🔴 Missing",
  IF(
    AND(
      {Picked Up}=FALSE(),
      {Dropped Off}=FALSE(),
      Stripped=FALSE()
    ),
    "⚪️ On Job Site",
    IF(
      AND(
        {Picked Up}=TRUE(),
        {Dropped Off}=FALSE(),
        Stripped=FALSE()
      ),
      "⚫️ Picked Up",
      IF(
        AND(
          {Picked Up}=TRUE(),
          {Dropped Off}=TRUE(),
          Stripped=FALSE()
        ),
        "🔵 Delivered",
        IF(
          AND(
            {Picked Up}=TRUE(),
            {Dropped Off}=TRUE(),
            Stripped=TRUE(),
            {UnProcessed Samples}!=0
          ),
          "🔘 Stripped/Marked",
          IF(
            AND(
              {Picked Up}=TRUE(),
              {Dropped Off}=TRUE(),
              Stripped=TRUE(),
              {UnProcessed Samples}!=0
            ),
            "☑️ Processed",
            "🔶 Sequence Error"
          )
        )
      )
    )
  )
)

OK - about the formula - I’m looking at the syntax. Are hard returns part of the syntax, or just written that way for ease of reading? Do you recommend writing the syntax in notepad before pasting into the field?

Your curly brackets reference “Picked Up”, “Dropped Off”, and “Unprocessed Samples” - are any of those linked fields for which the results might be more than true or false? Can I account for specific text, and not just blank or non-blank fields?

Finally - what is “Stripped” which could equal “True”? Stripped is not in curly brackets, so I’m assuming it’s not a field.

Argh! I’m barely able to manage if statements in Excel… now trying to learn how do do one in Airtable seems like a hardship, but unfortunately I’m not yet authorized to invest in Pro plan.

It’s a field, it couldn’t be other thing. The curly brackets are used for grouping multiwork field names.

No problem. We can get you up to speed :slightly_smiling_face:

A field name can be typed as is in a formula if it is a single word with no special characters.
Thus, “Stripped” is a field name – in this case, it is a check box field, so its only possible values are TRUE() or FALSE().

If a field name has a space or any special characters, it needs to be referenced in curly braces in a formula - {Picked Up}, {# Samples}, etc.

I definitely recommend using an external editor for writing big long formulas, and formatting them vertically, like so:

It makes it a thousand times easier to visualize what you are writing. Then, you can copy and paste into Airtable as is, and it will work fine. In that screenshot, I am using a free text editor built for coding called “Visual Studio Code”, available here. Notepad works too, but the advantage of an editor built for writing code is that it will auto-complete and auto-format parenthesis for you, making it much easier to keep track of them all to make sure statements are enclosed properly and that you aren’t missing any closing parenthesis.

The vertical formatting with hard returns is for ease of reading with long formulas. I also store all of my formulas in text file form so I can refer back to them if I need to change something.

All of the fields I used as examples happened to be check box fields, so their only possible values are TRUE() or FALSE(), but you can certainly check the value of a field against anything else as well, such as specific text enclosed in quotation marks, or math formulas/numbers.

For example,

IF(OR(Name = "Tamara", Name = "Jeremy"), "😄",IF(Name = "Elias", "🤨"))

Just messin, @Elias_Gomez_Sainz

IF(POWER({No. of Samples},3) >= 64, "All Good", "Too Few")

Keep asking questions as they come and we will try to help you get comfortable with doing what you need to do with formulas.

IF(OR(Name = "Tamara", Name = "Jeremy"), "😄",IF(Name = "Elias", "🤨"))

IF(POWER({No. of Samples},3) >= 64, "All Good", "Too Few")

Well - I thought I had the logic right but apparently not - the field won’t save. WHAT am I doing wrong? FYI - every time I try to use date functions I get an error, so I consider it highly possible that that’s what’s wrong and I just don’t know how to use the date function. But can you folks look and tell me what’s wrong with my logic?

if ({Expiration Date}<=Today()
and(
Or({Sponsor Level}=“Leader: $15,000+”,"{Sponsor Level}=“Leader 2yr: $13,000+”, “{Sponsor Level}= “Patron: 24,000+”,”{Sponsor Level}=“Patron 2yr: $22,000+”))
“Check Attributions”,
“Remove Attributions”)

Also - sorry that it’s not in nice readable format - I haven’t learned how to do that yet.

I see 2 mistakes at first sight:

• The syntax for AND and OR is: AND(Condition1, Condition2, ...)
• The field names goes without quotes, this is wrong: "{Sponsor Level}=“Leader 2yr: $13,000+”

If you surround your formula in triple back-ticks ( ``` ) it will post in a formatted code block.

In addition to what @Elias_Gomez_Sainz mentioned, I also see that you are trying to compare dates using math operators. That works in Excel, but not in Airtable. Airtable provides specific functions for working with Dates:

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

Here’s my attempt at interpreting your formula:

IF(
  AND(
    OR(
      IS_BEFORE(
        {Expiration Date},
        TODAY()
      ),
      IS_SAME(
        {Expiration Date},
        TODAY(),
        'day'
      )
    ),
    OR(
      {Sponsor Level} = "Leader: $15,000+",
      {Sponsor Level} = "Leader 2yr: $13,000+",
      {Sponsor Level} = "Patron: 24,000+",
      {Sponsor Level} = "Patron: 2yr: $22,000+"
    )
  ),
  "Check Attributions",
  "Remove Attributions"
)

Try pasting that in and see if it does what you need it to do.

Jeremy_Oglesby wrote:

If you surround your formula in triple back-ticks ( ``` ) it will post in a formatted code block.

In addition to what @Elias_Gomez_Sainz mentioned, I also see that you are trying to compare dates using math operators. That works in Excel, but not in Airtable. Airtable provides specific functions for working with Dates:

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

Here’s my attempt at interpreting your formula:

IF(
  AND(
    OR(
      IS_BEFORE(
        {Expiration Date},
        TODAY()
      ),
      IS_SAME(
        {Expiration Date},
        TODAY(),
        'day'
      )
    ),
    OR(
      {Sponsor Level} = "Leader: $15,000+",
      {Sponsor Level} = "Leader 2yr: $13,000+",
      {Sponsor Level} = "Patron: 24,000+",
      {Sponsor Level} = "Patron: 2yr: $22,000+"
    )
  ),
  "Check Attributions",
  "Remove Attributions"
)

Try pasting that in and see if it does what you need it to do.

Hi Jeremy! Thanks again for your help.

No - it almost worked, but it yields “Remove Attributions” in every instance except the ones that don’t have an expiration date. I’m not clear what ‘day’ means in the formula? It also looks backwards to me? I want to say if today is before the expiration date, but that looks like it says if the expiration date is before today. Ultimately it doesn’t matter - the goal is to check attributions if the sponsorship isn’t expired, and to remove them if it is - so I guess we can compare expiration date to today rather than today to expiration date, so long as we put the right results in the true/false columns. :slightly_smiling_face:

OH - and I wanted to know how (or where) to get colored buttons like you showed in your example. I tried to just create one, but I couldn’t figure out how to paste it into the formula, and your formula shows it as a character in quotation marks. Is there a set of callouts that Airtable recognizes that I can find somewhere for use in formulas?

Tamara

The formula IS_SAME({Expiration Date}, TODAY(), 'day') checks if the two dates given are the same accurate to the ‘day’ unit (you could put 'hour' in there and it would check if they are the same accurate to the hour, etc).

You tell me… that depends on what you want to do. The formula IS_BEFORE({Expiration Date}, TODAY()) checks if the first date passed in ({Expiration Date}) is before the second date passed in (TODAY()), and returns TRUE if it is, and FALSE if it is not.

The whole formula says:
IF it is true that the {Expiration Date} is EITHER on or before TODAY, AND it is also true that {Sponsor Level} matches one of the 4 options listed, THEN return “Check Attributions”… otherwise, return “Remove Attributions”.

Given that information, you should be able to adjust the location of dates in the formulas to get what you want.

I’m going to assume you are referring to emoji, like ❌ and ✅.

If you are on a Windows machine, I think the keyboard shortcut to open the emoji menu is Windows Key + .(period).

If you are on MacOS, the keyboard shortcut to open the emoji menu is ctrl + ⌘ + space bar.

AHA! :grinning: Thanks. Ok off to play with the logic again. (And re-read your post, to make sure I understood it)

OK - still not working, although I did get the icons to show! :slightly_smiling_face: Do I have to have a field called “Today” in order for this formula to work? Because I have an expiration date of 12/31/2018 that’s still coming up as Remove Attributions.

12/31/2018 is AFTER TODAY(), so it is failing the IS_BEFORE() check. Just swap the positions of {Expiration Date} and TODAY() in the formula and it will be checking that TODAY() is before or the same as {Expiration Date}.

And here I thought I excelled at logic. :persevere:

OK - that worked, and revealed another flaw in my logic - or rather, a forgotten piece, namely that all of the folks who aren’t Leaders or Patrons should have other flags, not “Remove Attributions”. But I’m going to see if I can follow the building logic and come up with the right fix.

Tamara

Alright - I gave it my best shot, and something is still wrong. Here’s what I put:

‘’‘IF(
AND(
OR(
IS_BEFORE(
TODAY(),
{Expiration Date}
),
IS_SAME(
{Expiration Date},
TODAY(),
‘day’
)
),
OR(
{Sponsor Level} = “Leader: $15,000+”,
{Sponsor Level} = “Leader 2yr: $13,000+”,
{Sponsor Level} = “Patron: 24,000+”,
{Sponsor Level} = “Patron: 2yr: $22,000+”
)
),
“ :green_heart: Check Attributions”,
If(OR(
IS_BEFORE(
TODAY(),
{Expiration Date}
),
IS_SAME(
{Expiration Date},
TODAY(),
‘day’
),“ :green_heart: ”,
“ :x: Remove Attributions”
) ‘’’
The goal is to say “remove attributions” for everyone who has expired, but to differentiate the non expires from the top 4 and everyone else. (top 4 say “ :green_heart: Check Attributions”, everyone else not expired say: “ :green_heart: ” .

I was trying to follow the instructions for nested “if” statements according to Airtable, but the syntax must be wrong, because I can’t save. My head may explode.

First, check for me if this works:

IF(
  AND(
    OR(
      IS_BEFORE(
        TODAY(),
        {Expiration Date}
      ),
      IS_SAME(
        {Expiration Date},
        TODAY(),
        'day'
      )
    ),
    OR(
      {Sponsor Level} = "Leader: $15,000+",
      {Sponsor Level} = "Leader 2yr: $13,000+",
      {Sponsor Level} = "Patron: 24,000+",
      {Sponsor Level} = "Patron: 2yr: $22,000+"
    )
  ),
  "💚 Check Attributions",
  IF(
    OR(
      IS_BEFORE(
        TODAY(),
        {Expiration Date}
      ),
      IS_SAME(
        {Expiration Date},
        TODAY(),
        'day'
      )
    ),
    "💚",
    "❌ Remove Attributions"
  )
)

(you were just missing a couple closing parentheses)

IF(
  AND(
    OR(
      IS_BEFORE(
        TODAY(),
        {Expiration Date}
      ),
      IS_SAME(
        {Expiration Date},
        TODAY(),
        'day'
      )
    ),
    OR(
      {Sponsor Level} = "Leader: $15,000+",
      {Sponsor Level} = "Leader 2yr: $13,000+",
      {Sponsor Level} = "Patron: 24,000+",
      {Sponsor Level} = "Patron: 2yr: $22,000+"
    )
  ),
  "💚 Check Attributions",
  IF(
    OR(
      IS_BEFORE(
        TODAY(),
        {Expiration Date}
      ),
      IS_SAME(
        {Expiration Date},
        TODAY(),
        'day'
      )
    ),
    "💚",
    "❌ Remove Attributions"
  )
)

Thanks - I suspected I was missing parenthesis, but I couldn’t see where. And… no - that still won’t save.

Sorry, Tamara,

I should have noticed before posting that I copied “smart quotes” out of your earlier post, and pasted them back in. Airtable doesn’t like “smart quotes” and rejects them in formulas for… reasons.

I’ve fixed all the smart quotes in the formula above, so you can try to copy-paste it as is again.

Worked! Thank you! What are “smart quotes”?

Tamara

A lot of text-processing software applications will seek to automatically detect the position of a quotation mark in the text, and based on it’s position, “curl” the quotation marks like you see there around my word “curl”, and around your words “smart quotes”. Those are smart quotes – it’s a purely aesthetic thing, that makes the quotation marks look better in the flow of text. However, they have a different unicode symbol representing them under the hood than "dumb" quotation marks, the ones that just go straight up and down, like you see around the word "dumb". Airtable only allows the unicode symbol for those dumb quotation marks in formulas.

When you type directly into the formula editor in Airtable, it produces dumb quotes; when you type into a code block (offset by triple back-ticks – ``` – ) here in these forums, it produces dumb quotes; when you type into a plain text editor like notepad or visual studio code, it produces dumb quotes.

However, when you type into most other formatted text applications, such as the post editing frame for this forum software, or MS Word, or your Email editor, you get smart quotes. If you copy and paste text out of something like that into the Airtable formula editor, you are copying and pasting the underlying unicode symbols for those characters (ie, you are often copying the unicode symbol for smart quotes, rather than the one for dumb quotes), and pasting those unicode symbols into Airtable’s formula editor. Same goes if you copy-paste text into a code block here in the forums.

You can tell the difference inside a code block by the coloration of text inside the quotation marks. When dumb quotes are used, the text inside turns red, indicating that the code block is recognizing a string surrounded by dumb quotes. However, the code block won’t recognize smart quotes, and so the text inside will not be colored red:

IF({Donor} = "24,000+ Annual", ...)

IF({Donor} = “24,000+ Annual”, ...)

I should have noticed, when I originally pasted my edited version of your formula back into the forums, that the strings did not print out in the color red – that was a clue to me that I had the wrong kind of quotes in my formula :slightly_smiling_face: