Create status fields with conditional logic
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"
)
)
References
Community discussion on conditional IF formulas for status fields: Requesting help with conditional IF formula for makeshift status field