Create status fields with conditional logic

Q: Should I use IF or SWITCH for status formulas in Airtable?

Use IF() for simple two-state checks (like done vs. not done). For three or more conditions, SWITCH(TRUE(), ...) is easier to read and maintain. Both produce the same result, but SWITCH keeps complex logic organized.

Q: Can I use conditional formulas with single select fields?

Yes. Reference the single select field directly in your formula: IF({Status} = "Active", "Open", "Closed"). You can also use SWITCH({Status}, "Active", "Open", "Closed", "Done", "Unknown") to map each option to a computed value.

Q: Why does my IF formula return blank instead of the expected status?

The most common cause is a blank or null field value that none of your conditions account for. Always add a default fallback at the end of your formula. With SWITCH, the last value (after all condition pairs) serves as the default. With IF, make sure your else branch handles the empty case.

Q: Can I combine a formula status field with colors or grouping in views?

Formula fields display as plain text, so they do not support the built-in color options of single select fields. However, you can group and filter views by formula field values. If you need colors, consider using the formula to write a value that matches a single select option, then copy it over with an automation.

You need a status field that updates automatically based on other field values. Instead of manually updating a status field, use formulas to calculate status from dates, checkboxes, numbers, or other fields.

Basic IF formula for status

The simplest status formula checks one condition:

IF({Completed}, "Done", "In Progress")

This returns "Done" if the {Completed} checkbox is checked, otherwise "In Progress".

Two-condition status (nested IF)

When you need to check multiple conditions, nest IF statements:

IF(
  {Completed},
  "Done",
  IF(
    {Started},
    "In Progress",
    "Not Started"
  )
)

This creates a three-state status:

"Done" if completed
"In Progress" if started but not completed
"Not Started" if neither

Multiple conditions with SWITCH

For more than 2-3 conditions, SWITCH() is cleaner than nested IFs:

SWITCH(
  TRUE(),
  {Completed}, "Done",
  {In Review}, "Reviewing",
  {Started}, "In Progress",
  "Not Started"
)

How it works:

SWITCH(TRUE(), ...) evaluates conditions in order
Returns the first matching value
The last value is the default fallback

Status based on dates

Overdue, due today, upcoming

SWITCH(
  TRUE(),
  AND({Due Date}, {Due Date} < TODAY()), "Overdue",
  AND({Due Date}, IS_SAME({Due Date}, TODAY(), 'day')), "Due Today",
  AND({Due Date}, {Due Date} > TODAY()), "Upcoming",
  "No Due Date"
)

Days until due

SWITCH(
  TRUE(),
  AND({Due Date}, {Due Date} < TODAY()), "Overdue",
  AND({Due Date}, DATEDIFF(TODAY(), {Due Date}, 'days') = 0), "Due Today",
  AND({Due Date}, DATEDIFF(TODAY(), {Due Date}, 'days') = 1), "Due Tomorrow",
  AND({Due Date}, DATEDIFF(TODAY(), {Due Date}, 'days') <= 7), "Due This Week",
  AND({Due Date}, DATEDIFF(TODAY(), {Due Date}, 'days') > 7), "Due Later",
  "No Due Date"
)

Status based on multiple fields

Combine conditions with AND() and OR():

All conditions must be true (AND)

IF(
  AND(
    {Approved},
    {Payment Received},
    {Contract Signed}
  ),
  "Ready to Start",
  "Pending"
)

Any condition can be true (OR)

IF(
  OR(
    {High Priority},
    {Urgent},
    {VIP Client}
  ),
  "Priority",
  "Standard"
)

Complex combinations

SWITCH(
  TRUE(),
  AND({Completed}, {Approved}), "Complete",
  AND({Completed}, NOT({Approved})), "Pending Approval",
  AND({Started}, {Payment Received}), "In Progress",
  {Payment Received}, "Ready to Start",
  "Not Started"
)

Status based on numeric values

Percentage complete

SWITCH(
  TRUE(),
  {Progress} >= 100, "Complete",
  {Progress} >= 75, "Almost Done",
  {Progress} >= 50, "Halfway",
  {Progress} > 0, "In Progress",
  "Not Started"
)

Score-based status

SWITCH(
  TRUE(),
  {Score} >= 90, "Excellent",
  {Score} >= 80, "Good",
  {Score} >= 70, "Fair",
  {Score} >= 60, "Needs Improvement",
  "Poor"
)

Status based on text fields

Contains specific text

SWITCH(
  TRUE(),
  FIND("Error", {Notes}), "Error",
  FIND("Warning", {Notes}), "Warning",
  FIND("Complete", {Notes}), "Complete",
  "Normal"
)

Exact match

SWITCH(
  {Category},
  "A", "High Priority",
  "B", "Medium Priority",
  "C", "Low Priority",
  "Unknown"
)

Status based on linked records

Count linked records

SWITCH(
  TRUE(),
  COUNT({Tasks}) = 0, "No Tasks",
  COUNT({Tasks}) = COUNT({Completed Tasks}), "All Complete",
  COUNT({Completed Tasks}) > 0, "In Progress",
  "Not Started"
)

Check if linked record exists

IF(
  {Assigned To},
  "Assigned",
  "Unassigned"
)

Priority status patterns

Urgency based on due date and priority

SWITCH(
  TRUE(),
  AND({High Priority}, {Due Date} < TODAY()), "Critical",
  AND({High Priority}, DATEDIFF(TODAY(), {Due Date}, 'days') <= 3), "Urgent",
  {High Priority}, "High Priority",
  AND({Due Date}, {Due Date} < TODAY()), "Overdue",
  "Normal"
)

Risk level

SWITCH(
  TRUE(),
  AND({Budget Overrun}, {Behind Schedule}), "High Risk",
  OR({Budget Overrun}, {Behind Schedule}), "Medium Risk",
  "Low Risk"
)

Workflow status patterns

Simple workflow

SWITCH(
  TRUE(),
  {Completed}, "Complete",
  {In Review}, "Review",
  {In Progress}, "Active",
  {Assigned}, "Assigned",
  "Backlog"
)

Approval workflow

SWITCH(
  TRUE(),
  AND({Submitted}, {Approved}), "Approved",
  AND({Submitted}, {Rejected}), "Rejected",
  {Submitted}, "Pending Review",
  {Draft}, "Draft",
  "Not Started"
)

Common patterns

Default to empty string

Instead of showing "Not Started" or "Unknown", return empty:

SWITCH(
  TRUE(),
  {Completed}, "Done",
  {Started}, "In Progress",
  ""
)

Include status emoji

SWITCH(
  TRUE(),
  {Completed}, "✅ Done",
  {In Review}, "👀 Reviewing",
  {Started}, "🔄 In Progress",
  "⏳ Not Started"
)

Status with count

IF(
  COUNT({Tasks}) > 0,
  "Active (" & COUNT({Tasks}) & " tasks)",
  "No Tasks"
)

Best practices

1. Use SWITCH for 3+ conditions

Nested IFs become hard to read. SWITCH(TRUE(), ...) is cleaner:

// Hard to read
IF({A}, "A", IF({B}, "B", IF({C}, "C", "Default")))

// Easier to read
SWITCH(TRUE(), {A}, "A", {B}, "B", {C}, "C", "Default")

2. Order conditions by priority

In SWITCH(TRUE(), ...), conditions are evaluated top to bottom. Put the most specific or important conditions first:

SWITCH(
  TRUE(),
  AND({A}, {B}), "Both",      // Most specific first
  {A}, "A Only",               // Then less specific
  {B}, "B Only",
  "Neither"                    // Default last
)

3. Handle blank values

Always account for blank or missing values:

SWITCH(
  TRUE(),
  AND({Due Date}, {Due Date} < TODAY()), "Overdue",
  {Due Date}, "Upcoming",
  "No Due Date"  // Handles blank
)

4. Keep status values consistent

Use the same status values across your base for easier filtering and grouping. Consider creating a single select field with your status options, then reference it in formulas if needed.

5. Test edge cases

Test your formulas with:

Blank/null values
Dates in the past, present, and future
Zero and negative numbers
Empty linked record lists

Troubleshooting

Status not updating

Problem: Status field shows old values.

Solution: Check if referenced fields are formula fields. Formula fields recalculate, but if they reference other formulas, there may be a delay. Also ensure your conditions are correctly structured.

Wrong status showing

Problem: Status doesn't match expected conditions.

Solution:

Check field names match exactly (case-sensitive)
Verify date comparisons account for time zones
Test each condition individually
Check for blank values interfering with logic

Formula too complex

Problem: Formula is hard to read or maintain.

Solution: Break into multiple formula fields, or use SWITCH() instead of deeply nested IF() statements.

Quick reference

Goal	Pattern
Simple two-state	`IF({Condition}, "Yes", "No")`
Three-state	`IF({A}, "A", IF({B}, "B", "C"))`
Multiple conditions	`SWITCH(TRUE(), {A}, "A", {B}, "B", "Default")`
All conditions true	`IF(AND({A}, {B}), "Both", "Not Both")`
Any condition true	`IF(OR({A}, {B}), "Either", "Neither")`
Date-based status	`SWITCH(TRUE(), {Date} < TODAY(), "Past", "Future")`
Count-based status	`SWITCH(TRUE(), COUNT({List}) = 0, "Empty", "Has Items")`
Text contains	`IF(FIND("Text", {Field}), "Found", "Not Found")`

When to use formulas vs single select

Use formula status when:

Status can be calculated from other fields
You want automatic updates
Status logic is complex but deterministic
You need consistency across records

Use single select status when:

Status requires manual input
Status doesn't depend on other fields
You need users to choose from a dropdown
You want to track status changes over time (with automations)

Advanced: Dynamic status with calculations

Status with percentage

IF(
  {Progress} >= 100,
  "Complete",
  {Progress} & "% Complete"
)

Status with time remaining

IF(
  AND({Due Date}, {Due Date} < TODAY()),
  "Overdue by " & DATEDIFF({Due Date}, TODAY(), 'days') & " days",
  IF(
    {Due Date},
    DATEDIFF(TODAY(), {Due Date}, 'days') & " days remaining",
    "No due date"
  )
)

FAQ

Should I use IF or SWITCH for status formulas in Airtable?

Use IF() for simple two-state checks (like done vs. not done). For three or more conditions, SWITCH(TRUE(), ...) is easier to read and maintain. Both produce the same result, but SWITCH keeps complex logic organized.

Can I use conditional formulas with single select fields?

Yes. Reference the single select field directly in your formula: IF({Status} = "Active", "Open", "Closed"). You can also use SWITCH({Status}, "Active", "Open", "Closed", "Done", "Unknown") to map each option to a computed value.

Why does my IF formula return blank instead of the expected status?