SharePoint calculated column formulas for due dates and status

Q: SharePoint calculated column days left or today not updating

Microsoft's pages say TODAY and ME are not supported in calculated columns, and a calculated column works from the values in its own row. For days left from today, use a view filter, column formatting or a flow that writes a number each day.

Q: SharePoint calculated column formula if empty

Use ISBLANK inside IF, as in Microsoft's example =IF(ISBLANK([Column1]), "Not OK", "OK").

Q: SharePoint calculated column concatenate

Join values with the ampersand, as in =[Column1]&" "&[Column2], or use CONCATENATE. Both patterns are in Microsoft's examples page.

Q: SharePoint calculated column multiple IF

Nest one IF inside another. Microsoft says a formula can have up to eight levels of nested functions, and its formula pages do not mention a SWITCH function.

Q: SharePoint calculated column with a lookup value

Microsoft says lookup fields are not supported in a formula, and a formula cannot reference another list or library.

Short answer: a SharePoint calculated column can use IF, AND, OR, ISBLANK, DATEDIF and & to join text, but Microsoft's formula pages say TODAY and ME are not supported in calculated columns. For anything based on today's date, use a view filter, column formatting or a Power Automate flow. Use calculated columns for the parts that stay the same once the row is saved.

What teams want

Teams ask for two things:

A number that says how many days are left.
A short word that says where an item stands, like "Late", "Due soon" or "Done".

The second one is mostly possible. The first one is not, at least not inside a calculated column. This guide shows what a calculated column can do, what it cannot, and where to go for the rest.

The limits, in plain words

Microsoft's page "Introduction to SharePoint formulas and functions" lists these limits:

TODAY and ME are not supported in calculated columns. They work in the default value setting of a column.
A formula cannot use a value from a row other than the current row.
A formula cannot use a value from another list or library.
A formula cannot use the ID of a row that was just added, because the ID does not exist yet when the calculation runs.
A formula can have up to eight levels of nested functions.
If a function returns the wrong kind of value for the place it is used, the list shows a #VALUE! error.

Microsoft's page on the TODAY function says the same thing: "You can use the TODAY function only as a default value; you cannot use it in a calculated column." That page, and the introduction page, list SharePoint Server versions and do not name SharePoint Online. Test on your own list before you rely on either limit.

What this means for you: a "Days left" column that counts down each morning cannot be built as a calculated column. A column that says "Late" when the due date has passed cannot be built either, because the answer depends on today's date. The page also says lists do not support the RAND and NOW functions.

Some forum posts claim a TODAY formula works for them. Microsoft's own pages say it is not supported, so do not build a tracker on it.

Where days left and "Late" should come from

Pick one of these instead:

A view filter. In the classic view editor (Edit current view, then Filters), you can filter on [Today]. This shows only late items. It works only in the classic view editor, and exact names may differ.
Column formatting. This can color a row red when the due date is past. See "Turn overdue rows red in a SharePoint list".
A scheduled flow. A flow can check dates each day and message the owner. See "Send a reminder before a due date in a SharePoint list".

For step-by-step workarounds, see "Use today's date in a SharePoint calculated column".

Formulas that Microsoft documents

Microsoft's page "Examples of common formulas in SharePoint lists" shows these patterns. Column1, Column2 and Column3 stand for your own column names. Put the formula in a calculated column and set its return type to match the result (Single line of text for words, Number for a count of days, Date and Time for dates).

Compare two values and return words:

=IF([Column1]<=[Column2], "OK", "Not OK")

Check that two things are both true:

=AND([Column1]>[Column2], [Column1]<[Column3])

Check that at least one thing is true:

=OR([Column1]>[Column2], [Column1]<[Column3])

Check for an empty cell:

=IF(ISBLANK([Column1]), "Not OK", "OK")

Count the days between two dates:

=DATEDIF([Column1], [Column2],"d")

Join two values into one text:

=[Column1]&" "&[Column2]

When a formula returns a date, Microsoft says the return type must be "Date and Time". A formula that counts days, such as DATEDIF, returns a number, so set its return type to Number.

Everything below is built from these patterns. Because your column names and choices differ, treat every formula in the next section as a starting point. Exact formula may differ; test on a copy.

More patterns

Join text. Microsoft's examples use the ampersand or CONCATENATE, for example =[Column1]&" sold "&[Column2]&" units." and =CONCATENATE([Column1]," sold ",[Column2]," units.").

Blank check. Use ISBLANK, as in the examples above: =IF(ISBLANK([Column1]), "Not OK", "OK").

Three or more outcomes. Put one IF inside another. Microsoft says "A formula can contain up to eight levels of nested functions." Microsoft's formula pages do not mention a SWITCH function, so use nested IF. Pattern, with your own names:
=IF([Status]="1 Open","Working",IF([Status]="2 In progress","Started","Closed"))
Exact formula may differ; test on a copy.

Date as text. Microsoft's examples use the TEXT function to show a date inside a sentence: =[Column1]&" "&TEXT([Column2], "mmm-dd-yyyy"). For a date result instead of text, Microsoft says the return type must be "Date and Time".

Lookup columns. Microsoft says "Lookup fields are not supported in a formula." A formula also cannot read another list.

Days left. Our advice, not a Microsoft rule: add a Number column named Days left, and let a scheduled flow fill it.

Create a scheduled cloud flow that runs once a day.
Add the SharePoint action Get items. Set Top Count and turn Pagination on.
Inside an Apply to each loop, add Update item. Write the number of days between today and the Due date into Days left.
Test on a copy of the list. A flow that runs on a schedule does not wake itself up when an item changes, so it does not need a loop guard. A second flow that runs when an item is modified does need one.

Worked example: a Tasks list

Build a list with these columns:

Title, the task name.
Due date, a Date column.
Status, a Choice column with the numbered choices "1 Open", "2 In progress" and "3 Done".
Created, which SharePoint adds on its own. Exact names may differ.

Add two calculated columns. Make a copy of the list first.

Status text

Name it "Status text". Set the return type to Single line of text. Formula to try:
=IF([Status]="3 Done","Closed",IF(ISBLANK([Due date]),"No due date","Open"))

It reads like this: if the status is 3 Done, show Closed. If there is no due date, show No due date. Otherwise show Open. Notice the choice is written exactly as "3 Done", with the number. If your choice says something else, the formula will not match. Exact formula may differ; test on a copy.

This column does not say "Late". It cannot, for the reasons above. Use the red-row formatting for that.

Priority from the length of the task window

Name it "Priority". Set the return type to Single line of text. The idea: the fewer days between the day the task was created and the due date, the higher the priority. Formula to try:
=IF(ISBLANK([Due date]),"3 Low",IF(DATEDIF([Created],[Due date],"d")<=2,"1 High","2 Medium"))

It reads like this: no due date means 3 Low. A window of two days or less means 1 High. Anything else is 2 Medium. The cut-off of two days is only an example. One way to start is to pick the number your team already uses in its head. Exact formula may differ; test on a copy. Test a due date that comes before the created date too.

Step by step

Open the list and make a copy to test in.
Add the Status choices with number prefixes, as above.
Add a column, choose Calculated, and give it a name.
Paste the formula. Change column names to match yours exactly, including spaces.
Set the return type, then save.
Add three test rows: one with no due date, one done, one open with a short window. Check each result by eye.
If you see #VALUE!, a part of the formula returns the wrong kind of value. Remove parts until it clears, then add them back one by one.
When all test rows are right, repeat on the real list.

What to avoid

Do not use TODAY or ME in a calculated column. Microsoft's pages say they are not supported there.
Do not expect a calculated column to change by itself as days pass. Its answer is worked out from the values in the row, so it does not know that a new day started.
Do not nest more than eight levels of functions. Split the work into two columns instead.
Do not point a formula at another row or another list. It will not work.
Do not write the choice text from memory. "3 Done" and "Done" are different words to a formula.

Honest limits

A calculated column is good at wording and simple math on one row. It is not a clock. If your team needs live countdowns, alerts or a "Late" flag that changes by itself, plan on a view filter, formatting or a flow from the start. Also check which of your columns can be used in a formula; some column types may not be allowed. Exact names may differ on your screen.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

SharePoint calculated column days left or today not updating