Common Calculated Column Formulas in SharePoint for Quick Implementations

A Calculated Column derives its value from other columns on the same item, using Excel-like formula syntax — no Power Automate flow or code required. Here are the formulas worth knowing, and the real limitations that decide when a calculated column is the wrong tool.

In this post: Common formulas · Real limitations · Creating them with PnP PowerShell · Chaining calculated columns: order matters · Best practices · Pros and cons · Alternatives · Worked example: Days Until Due · When would you actually use this? · Related reading

Common formulas

Status indicator:

=IF([Percent Complete]=1, "Complete", "In Progress")

Due date warning:

=IF([Due Date]<TODAY(), "Overdue", "On Track")

Date difference in days:

=[End Date]-[Start Date]

Concatenate text:

=[First Name] & " " & [Last Name]

Priority flag:

=IF([Priority]="High", "Needs immediate attention", "Standard")

Month name from a date:

=TEXT([Created],"mmmm")

Real limitations

Worth knowing before building around calculated columns, not after: they can’t reference lookup columns, person/group fields, or external data — only other columns on the same item, of the types the formula language supports. They’re also not editable in Power Apps forms and don’t trigger Power Automate flows directly — if a change needs to kick off a workflow, the calculated column itself won’t do it.

The single most common gotcha: TODAY() does not refresh dynamically inside a calculated column. The value is computed once, when the item is created or last modified — it doesn’t recalculate itself daily just because a day passed. A “days overdue” column built naively with TODAY() will silently show stale numbers until the item is edited again. The common workaround uses a dummy column and view-level formatting instead of relying on the calculated column to stay current — see this StackExchange thread for the specific technique.

Creating them with PnP PowerShell

Rolling the same calculated column out to several lists (or several sites) by hand through the UI doesn’t scale — Add-PnPField scripts it, with -ResultType telling SharePoint what data type the formula’s output should be treated as:

Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive

Add-PnPField -List "Tasks" -Type Calculated -DisplayName "Status Label" `
    -InternalName "StatusLabel" -ResultType Text `
    -Formula '=IF([Percent Complete]=1,"Complete","In Progress")'

Worth knowing before relying on this for anything complex: Add-PnPField has had real, documented reliability issues specifically with calculated columns referencing other field types (a formula that works fine when built through the UI sometimes returns 0 or blank when created this way). If a scripted calculated column isn’t computing correctly, Add-PnPFieldFromXml — passing the full field schema as raw XML rather than individual parameters — is the more reliable fallback, at the cost of writing out the XML by hand instead of a single cmdlet call.

Chaining calculated columns: order matters

Worth knowing since it’s easy to assume otherwise from the “Real limitations” section above: a calculated column can reference another calculated column — that’s not one of the blocked field types. The catch is entirely about timing, not capability: the referenced column has to already exist at the moment the new formula is created and validated, or SharePoint rejects it with “The formula refers to a column that does not exist,” even though the column name is spelled correctly and will exist a moment later.

This bites hardest exactly where the PnP PowerShell section above is most useful — scripting several related calculated columns in one pass. Creating them in the wrong order in the same script fails the same way clicking through the UI in the wrong order would:

# Wrong order -- DaysOverdue references StatusLabel, which doesn't exist yet
Add-PnPField -List "Tasks" -Type Calculated -DisplayName "Days Overdue" `
    -InternalName "DaysOverdue" -ResultType Text `
    -Formula '=IF([StatusLabel]="Overdue",TODAY()-[Due Date],"")'   # fails here

Add-PnPField -List "Tasks" -Type Calculated -DisplayName "Status Label" `
    -InternalName "StatusLabel" -ResultType Text `
    -Formula '=IF([Due Date]<TODAY(),"Overdue","On Track")'

# Correct order -- create the column being referenced first
Add-PnPField -List "Tasks" -Type Calculated -DisplayName "Status Label" `
    -InternalName "StatusLabel" -ResultType Text `
    -Formula '=IF([Due Date]<TODAY(),"Overdue","On Track")'

Add-PnPField -List "Tasks" -Type Calculated -DisplayName "Days Overdue" `
    -InternalName "DaysOverdue" -ResultType Text `
    -Formula '=IF([StatusLabel]="Overdue",TODAY()-[Due Date],"")'

The same dependency ordering matters when migrating calculated columns between environments, not just creating them fresh — exporting and reimporting a set of interdependent calculated columns in whatever order they happen to come back needs the same upstream-before-downstream sequencing, or the import fails on exactly the same “column doesn’t exist” error.

Best practices

Keep formulas simple — a calculated column with deeply nested IF statements is hard to maintain and debug later.
Reference columns by internal name, not display name, in the formula — consistent with how every other SharePoint API/formula context works.
Don’t rely on TODAY() for anything that needs to stay current — use the dummy-column workaround, or handle date-relative logic in a view/Power Automate instead.
Choice fields work more predictably as plain text in formula comparisons than as their underlying choice objects.

Pros and cons

Pros	Cons
No code or flow required	Limited data types — no lookups or person fields
Instantly reflected in list views	TODAY() doesn’t recalculate dynamically
Improves list readability and filtering	Can’t trigger flows or automation
Easy for non-developers to set up	Not editable in Power Apps forms

TODAY() does not refresh dynamically inside a calculated column — the value is computed once, when the item is created or last modified, not recalculated daily.

Alternatives

Method	When to use	Tradeoff
Power Automate	Needs to trigger other actions, or reference lookups/external data	More setup, sometimes needs premium licensing
Power Apps formula	Custom form logic	Not reflected in the list view itself
JSON column formatting	Visual indicators (color, icons) without a new computed value	Display-only, doesn’t compute or store a value
SPFx extension	Logic beyond what formulas or JSON can express	Requires real development work

Worked example: Days Until Due

Create a new calculated column named DaysLeft.
Formula: =[Due Date]-TODAY() — remembering the caveat above: this won’t auto-update daily without the item being re-saved.
Add JSON view formatting on top to color-code the result: negative values red (overdue), under 3 yellow (urgent), 3 or more green (on track).

Reference: Microsoft’s official formula syntax guide.

When would you actually use this?

A list needs a status or label derived from other fields, purely for display — no automation needed, just a readable computed value.
You want filterable/sortable “days until due” or “days overdue” data without standing up a Power Automate flow for it.
You’re combining fields (first/last name, address parts) into one display value for a view, without a flow triggering on every edit.

References: Calculated column formula reference, List view JSON formatting samples.

SharePoint List View Formatting Overview — the JSON formatting layer this post’s alternatives and worked example both lean on.
Understanding SharePoint List Types — background on the list/library structures calculated columns get added to.

Good for quick, no-code logic — just know where the real limits are before building something more critical around one. Questions? Comment below.