Re: 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"
)
)
)
)
)
)
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 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.
