If you’ve tried to tag every task that mentions “laptop” with a SharePoint calculated column, you’ve probably found there’s no CONTAINS function, and FIND quietly misses “LAPTOP” in capitals. The fix is =IF(ISNUMBER(SEARCH("laptop",[Task])),"Hardware","Other"), because SEARCH ignores case.
Most formula problems look like that: almost right, broken by one function, one data type or one unsupported column.
In this tutorial, we’ll build a SharePoint calculated column in a modern list, pick the return type, and work through tested formulas: IF with AND and OR, if contains text, text, dates, numbers, errors and Power Automate. Every result comes from a real list I built on 11/03/2026.
What Is a SharePoint Calculated Column (and What It Can’t Do)?
A calculated column shows a value SharePoint works out from other columns in the same item, using Excel-style functions. Nobody types into it; it’s recalculated whenever the item is saved and works in SharePoint list views, filters and sorting.
Use it to show something. To block bad data, use SharePoint column validation formulas instead. Here’s what a calculated column can’t do, with the exact errors from my tests:
| What you try | What happens |
|---|---|
Use [Today], TODAY() or [Me] | “Calculated columns cannot contain volatile functions like Today and Me.” |
| Reference a Person, Lookup or Multiple lines of text column | “One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.” |
| Read another item or another list | Not possible: a formula only sees its own item |
Use [ID] | A new item gets 0, so =[ID]+1000 showed 1000 |
Need today’s date? Filter a view with [Today], or let a flow write it into a normal column, as in updating a SharePoint field with today’s date in Power Automate.
The Sample List We’ll Use
Every formula runs on one list, SPGDemo Calculated Columns, a six-task project tracker (new to lists? see my Microsoft Lists tutorial). Columns: Task, First Name, Last Name, Email, Department, Priority, Status, Start Date, Due Date, Hours, Budget and Spent.
Three rows are tricky on purpose: “LAPTOP docking stations for sales floor” is in capitals, “CRM training for new reps” has a $0 budget, and “Trade show booth design” spent $4,650 of $4,200. That’s where formulas usually break.

How to Create a Calculated Column in a Modern SharePoint List
- Open the list and select + Add column. The Create a column dialog opens with a list of column types.
- Scroll to the bottom of that list, select See all column types, then select Next. The classic Create Column page opens.
- Type a column name, such as Escalation, and pick Calculated (calculation based on other columns).
- Type the formula in Formula, starting with = and with column names in square brackets. Double-click a name in Insert Column to avoid typos.
- Pick the return type under The data type returned from this formula is, then select OK.


Steps 4 and 5 happen lower on the same page. The return type decides how the result is shown:

| Return type | Use it for | Example |
|---|---|---|
| Single line of text | Labels, joined text, formatted dates | =IF(AND([Priority]="High",[Status]<>"Completed"),"Escalate","OK") |
| Number | Counts, day differences, ratios | =DATEDIF([Start Date],[Due Date],"d") |
| Currency | Money math | =[Budget]-[Spent] |
| Date and Time | Adding days or months | =[Due Date]+7 |
| Yes/No | True/false tests | =[Spent]>[Budget] |
Pro Tip: Insert Column only lists columns a formula can use. My Assigned To (Person) and Notes (multiple lines) columns never appear there.
Method 1: IF, AND and OR Formulas
Use IF when the result depends on a condition. Same SPGDemo list: flag high-priority tasks that aren’t finished (return type: Single line of text):
=IF(AND([Priority]="High",[Status]<>"Completed"),"Escalate","OK")
That returns Escalate for “Replace laptop for new hire” and “Payroll system upgrade”, and OK for the other four.

How does this work?
AND is TRUE only when both tests pass. “Trade show booth design” is High priority too, but it’s Completed, so [Status]<>"Completed" fails and it gets OK. <> means “not equal”, and text goes in double quotes.
OR needs just one passing test, and nested IFs give three or more outcomes:
=IF(OR([Department]="IT",[Department]="Finance"),"Back office","Front office")
=IF([Hours]>40,"Large",IF([Hours]>16,"Medium","Small"))
Three tasks are Back office (IT and Finance). The 60- and 45-hour tasks are Large, 24 and 18 Medium, 12 and 8 Small. Put the biggest threshold first, because SharePoint stops at the first match.
Method 2: SharePoint Calculated Column If Contains Text
CONTAINS doesn’t exist in list formulas; I tried it and got “The formula contains a syntax error or is not supported.” Wrap SEARCH (or FIND) in ISNUMBER instead. Same list, Task column:
=IF(ISNUMBER(SEARCH("laptop",[Task])),"Hardware","Other")
Both laptop rows come back as Hardware, including the capitalized one. With FIND, that LAPTOP row turns into Other:
=IF(ISNUMBER(FIND("laptop",[Task])),"Hardware","Other")

How does this work?
SEARCH returns where the text starts (9 for “Replace laptop”) or an error when it’s missing. ISNUMBER turns that into TRUE or FALSE, and IF picks the label. FIND matches case exactly, so use SEARCH unless case matters.
For several words, nest the tests:
=IF(ISNUMBER(SEARCH("laptop",[Task])),"Hardware",IF(ISNUMBER(SEARCH("training",[Task])),"Training","Other"))
Two tasks become Hardware, “CRM training for new reps” becomes Training, and three are Other.
Method 3: Text Formulas (&, CONCATENATE, LEFT, RIGHT and MID)
Text formulas join or split what people typed. Same list, name and email columns:
=[First Name]&" "&[Last Name]
=CONCATENATE([Last Name],", ",[First Name])
=RIGHT([Email],LEN([Email])-FIND("@",[Email]))
=LEFT([Email],FIND("@",[Email])-1)
For row 1 those return Emily Carter, “Carter, Emily”, spguides.com and emily.carter.

How does this work?
& and CONCATENATE both glue text together. For emily.carter@spguides.com, FIND(“@”) returns 13 and LEN returns 25, so RIGHT keeps the last 12 characters and LEFT the first 12.
Functions combine, too. This task code uses the department, start year and last name:
=UPPER(LEFT([Department],3))&"-"&TEXT([Start Date],"yyyy")&"-"&MID([Last Name],1,3)
That gives IT-2026-Car, FIN-2026-Ben and MAR-2026-Ram.
Method 4: Date Formulas (DATEDIF, Add Days and TEXT)
With dates, the return type matters most. Same list, Start Date and Due Date. Days between two dates (Number):
=DATEDIF([Start Date],[Due Date],"d")
01/05/2026 to 01/16/2026 shows 11. To add days, add a number and set the return type to Date and Time, or you’ll see a serial number:
=[Due Date]+7
A 01/16/2026 due date gives a 01/23/2026 review date.

How does this work?
SharePoint stores dates as day numbers, so +7 moves a week. TEXT formats a date as words, and DATE handles months of different lengths:
=TEXT([Due Date],"mmmm yyyy")
=TEXT([Due Date],"dddd")
=DATE(YEAR([Due Date]),MONTH([Due Date])+1,DAY([Due Date]))
A 02/24/2026 due date returns February 2026 and Tuesday; 07/31/2026 plus one month is 08/31/2026.
Important: dates follow the site’s time zone. If one shows a day early, check SharePoint site regional settings.
Method 5: Number and Currency Formulas
Math works like Excel. Same list, Hours, Budget and Spent:
=[Budget]-[Spent]
=[Spent]>[Budget]
=ROUND([Spent]/[Hours],2)
Remaining (Currency) is $6,300.00 for the payroll upgrade and ($450.00) for the over-budget trade show, which also shows Yes in the Yes/No column. Cost per hour is $120.83 for the laptop task.

How does this work?
Currency columns hold plain numbers; the Currency return type only adds the $ sign, separators and brackets for negatives. A bare comparison returns TRUE or FALSE, shown as Yes or No.
Fix Calculated Column Errors (#DIV/0!, Syntax Errors and Semicolons)
Same list: =[Spent]/[Budget] works on five rows, but the $0-budget row shows #DIV/0!. Guard the division with ISERROR:
=IF(ISERROR([Spent]/[Budget]),"No budget",TEXT([Spent]/[Budget],"0%"))
That row now reads No budget, and the others show 81%, 0%, 111%, 76% and 34%.

These save-time errors came up in my tests:
| Error message | Cause | Fix |
|---|---|---|
| The formula contains a syntax error or is not supported. | Misspelled function (IFF), CONTAINS, or semicolons on a US-English site | Fix the name, use ISNUMBER(SEARCH()), use commas |
| The formula refers to a column that does not exist. | A typo like [Hourz], or unquoted text (Large instead of “Large”) | Check Insert Column; quote text |
| One or more column references are not allowed… | Person, Lookup or Multiple lines of text column | Copy the value to a supported column |
| Calculated columns cannot contain volatile functions like Today and Me. | [Today], TODAY() or [Me] | Use a view filter instead |
Microsoft’s older calculated field reference notes that sites in countries using a comma as the decimal mark take semicolons between arguments, so swap them on those sites. For [Me], see filtering a list view by the current user.
#NAME? never appeared in my tests, because misspelled functions are rejected at save time. #VALUE! did: =VALUE([Task]) showed it on every row, since a task name isn’t a number.
Add a Calculated Column with PnP PowerShell
I built the demo columns with PnP PowerShell (see creating a SharePoint list and columns with PnP PowerShell and adding list items with PnP PowerShell). One catch: escape the formula.
# Add-PnPField puts the formula into field XML as-is: write & as & and <> as <>
Add-PnPField -List "SPGDemo Calculated Columns" -DisplayName "Escalation" -InternalName "Escalation" `
-Type Calculated -ResultType Text -AddToDefaultView `
-Formula '=IF(AND([Priority]="High",[Status]<>"Completed"),"Escalate","OK")'
Unescaped & or <> failed with “An error occurred while parsing EntityName”. The script also accepted TODAY(), but the value froze when I added the column, which is why the browser blocks it.
Read a Calculated Column in Power Automate
Power Automate can read a calculated column but not write to it. The Get items action calls the SharePoint REST API, which returned row 1 like this:
{
"Title": "Replace laptop for new hire",
"DurationDays": "11.0000000000000",
"OverBudget": "0",
"Escalation": "Escalate"
}
Numbers come back as text and Yes/No as “0” or “1”, so convert before comparing:
float(item()?['DurationDays'])
Filtering on Escalation through the REST API failed with “The field ‘Escalation’ of type ‘Calculated’ cannot be used in the query filter expression.” Keep calculated columns out of Filter Query and use a Filter array action after Get items:
@equals(item()?['Escalation'], 'Escalate')
On my list, that keeps the two Escalate rows.
Which Formula Should You Use?
| You want to | Use |
|---|---|
| Label items by conditions | IF with AND / OR, nested IF |
| Check if text contains a word | IF(ISNUMBER(SEARCH(…))) |
| Join or split text | &, CONCATENATE, LEFT, RIGHT, MID, FIND |
| Count or add days and months | DATEDIF, [Date]+n, DATE() |
| Money and math | + – * /, ROUND, ISERROR |
Working in Power BI? That’s DAX; see calculated columns based on another column in Power BI. More list guides are in the SharePoint tutorials hub.
Things to Keep in Mind
- Return type first: date math needs Date and Time.
- Values update only on save, which is why [Today] isn’t allowed.
- SEARCH, not FIND, for contains checks unless case matters.
- Guard division with ISERROR to avoid #DIV/0!.
- No Filter Query on calculated columns in Power Automate; use Filter array.
Want to keep going with list columns? These pick up where this one stops:
Frequently Asked Questions
How do I check if a SharePoint calculated column contains text?
Use IF with ISNUMBER and SEARCH, for example =IF(ISNUMBER(SEARCH(“laptop”,[Task])),”Hardware”,”Other”). SEARCH ignores case. Use FIND only when the capitalization must match exactly.
Can a SharePoint calculated column use today’s date?
No. SharePoint rejects [Today], TODAY() and [Me] with the error “Calculated columns cannot contain volatile functions like Today and Me.” Filter a view with [Today], or use a scheduled flow that writes today’s date into a normal date column.
Which functions can I use in a SharePoint calculated column?
Most Excel-style functions work: IF, AND, OR, ISNUMBER, SEARCH, FIND, LEFT, RIGHT, MID, LEN, CONCATENATE, TEXT, DATEDIF, DATE, ROUND and ISERROR. Microsoft lists them in Examples of common formulas in lists.
Can a calculated column reference a lookup or person column?
No. Lookup, Person and Multiple lines of text columns can’t be used in formulas, and a calculated column can’t read other items or lists. Copy the value into a supported column with a flow first.
Can Power Automate update a SharePoint calculated column?
No. Calculated columns are read-only. A flow can read the value with Get items, but it should change the source columns; SharePoint recalculates the result when the item is saved.

Hey! I’m Bijay Kumar, founder of SPGuides.com and a Microsoft Business Applications MVP (Power Automate, Power Apps). I launched this site in 2020 because I truly enjoy working with SharePoint, Power Platform, and SharePoint Framework (SPFx), and wanted to share that passion through step-by-step tutorials, guides, and training videos. My mission is to help you learn these technologies so you can utilize SharePoint, enhance productivity, and potentially build business solutions along the way.
Thank you so much, this was very helpful!
Hi, What would be the easiest way to make a calculated field that adds 1 day to a date( in a column e.g called “Start Up”)
Great job! Very useful information
Thanks!
Hi Guys,
I’m using sharepoint 2013 and sharepoint designer 2013 and infopath designer and filler 2013.
I have calculated fields that are populating values post submitting the report. I heard that there is no way to preview the results of calculated columns using the 2013 version of above applications mentioned.
I want to know if we have any options to use Lastsubmit() function in Info-path or sharepoint designer like we use in PowerApps.
How to get Title and created by……
Did you ever find out?
any one plese tell how to genedrate unique id in list starting from 001
to 999
Power automate could be your option- when an item is created , use initial variable select the columns with- between them and use share point ID. gives you a Miquelon reference number. Try this link https://www.google.co.uk/search?q=power+automate+unique+reference&ie=UTF-8&oe=UTF-8&hl=en-gb&client=safari#fpstate=ive&vld=cid:88d24820,vid:vX8Vf06AH_o,st:220
Hi,
Looking to create an automated column showing the year , taken from the ‘Date’ column. Hoping to keep the calculated year column as null when the ‘Date’ is blank. Not having 30/12/1899.
Could anyone please show me what I would need to change?
=IFBLANK(Date),””;TEXT(Date,”yyyy”)
I’m trying to create a calculated field that looks at two columns to calculate a priority – one is a number column the other is an text column (for impact). If the number column is less than 7 and the impact column is ‘Case Cancellation’, I want the priority to be “high” – what formula should I use to accomplish this?
Hi, I spent my whole day looking how to fix this half day leave. Then i found your post… it works like magic… Thank You so much.
But I have a problem.. If i select start date as 16 and end date as 16 if i am taking full day leave, the total days should be 1 but it shows 0 in my case..
My question is can i add two formulas in 1 column, like if TRUE then +0.5 and if FALSE then +1…. that will work wonders if i can do that.
Thank you in advance.
Hi, I need to calculate number of days from the date the entry was made to ‘Todays date’, is there a formula to use ‘Todays date’ please? Currently I have this
=DATEDIF([Date reported],[today],”d”) but Sharepoint is not recognising [today] as a date. Many thanks