← Al Davies, 250 High Street

Use today's date in a SharePoint calculated column

Short answer: a SharePoint calculated column cannot use TODAY or NOW to show a live date. Microsoft says: "The TODAY and ME functions are not supported in calculated columns but are supported in the default value setting of a column." To show which items are late, use a view filter on [Today], column formatting with @now, or a Yes/No flag column that a small flow keeps current.

What people try

You have a Due date column. You want a column that shows "Late" or "Days left" without anyone typing. The idea is simple: compare the due date to today. In a spreadsheet that works. In a SharePoint calculated column, it does not.

A calculated column is a column whose value comes from a formula that uses other columns in the same row. Today's date is not a column in the row, so the formula has nothing to read. Microsoft's page on the TODAY function says: "You can use the TODAY function only as a default value; you cannot use it in a calculated column."

What Microsoft says

Two Microsoft Support pages state this limit:

The first page also says "You cannot reference another column in a formula that creates a default value for a column." That matters below, because it rules out one tempting plan. For the formulas a calculated column can run, see "SharePoint calculated column formulas for due dates and status".

Our advice, not a Microsoft rule: do not build a tracker on a forum post that says a TODAY formula works for someone. Use a method Microsoft's own pages support.

Four ways to get what you wanted

  1. A default value. Good for stamping the date an item was added.
  2. A view filter with [Today]. Good for a view that shows only late items.
  3. A Yes/No flag column. Good when you also need old rows to drop off, or a column you can sort and count.
  4. Column formatting. Good for coloring late rows red.

Option 1. Use TODAY as a default value

A default value fills a column when someone creates a new item. TODAY is allowed there. Use it for a "Date requested" column. Exact names may differ in your version of SharePoint, so look in the column settings for the default value box.

Two limits to know:

For the full pattern with the current user, see "Default a SharePoint column to today or the current user".

Option 2. Filter a view with [Today]

A view can show only the items whose due date is before today. The filter works on the current date every time someone opens the view.

Microsoft's filtering page says: "There are two keywords that you can use to filter based on the current day [Today], or the current user [Me]." It also says: "You must use the classic view editor to filter columns using these keywords." And: "It is not possible to use the [Today] or [Me] filters when filtering using a column header."

Here is a worked example. The list is called Tasks. It has these columns:

Steps:

  1. Open your list in SharePoint.
  2. Create a new view named "Overdue", or open the one you have.
  3. Select the name of your current view in the view menu, then choose Edit current view. Exact names may differ.
  4. In the Filters section, choose "Show items only when the following is true".
  5. Add a statement: Due date is less than [Today].
  6. Add a second statement joined by And: Status is not equal to 2 Done.
  7. Add a third statement joined by And: Status is not equal to 3 Dropped.
  8. Sort by Due date, oldest first.
  9. Select OK.

Microsoft says: "You can have up to 10 statements in a filter definition." Three is well inside that.

Our advice, not a Microsoft rule: set blank and "is not equal to" filters in the classic view editor too.

Microsoft also gives a date range example: "To leave out the current day, set a second filter on the Date column to be less than [Today] and connect them with And." Use the same idea if you want "due in the last 30 days" instead of "everything older than today".

Option 3. Add a Yes/No flag column

A view filter on [Today] has one weakness. It shows every open item whose due date is in the past, forever, until someone sets Status. Our advice, not a Microsoft rule: add a Yes/No column named "Closed" and filter on it, so old rows drop off when the work is finished.

A second flag is "Late". A daily flow can set it to Yes when the due date has passed. Then you can sort, group and count late items in any view, and send a message based on it.

Set it up like this:

  1. Add a Yes/No column named "Closed". Set the default to No.
  2. Add a Yes/No column named "Late". Set the default to No.
  3. In your Overdue view, filter on Closed equals No and Due date is less than [Today].
  4. In Power Automate at https://make.powerautomate.com, create a scheduled flow that runs once a day.
  5. Add a Get items action for your list. In Filter Query, use a range on the date, not an equals test: Due_x0020_date lt 'TODAY' and Closed eq 0. For TODAY use formatDateTime(utcNow(),'yyyy-MM-dd'). Spaces in a column name become _x0020_ in the internal name, so check the name in the column settings. Our advice, not a Microsoft rule: test the Closed part on one row first.
  6. In Get items, set Top Count to a number large enough for your list, up to 5,000, and turn Pagination on. The default is 100 rows. For the full steps, see "Power Automate Get items stops at 100 rows: how to get them all".
  7. Add an Apply to each loop, then an Update item action that sets Late to Yes. If Title is required, Update item asks for it too, so pass the Title from Get items. This flow starts on a schedule, not on "item created or modified", so it does not trigger itself.
  8. Run the flow once with the Test button and check three items.

The flow runs once a day, so the flag can be up to a day behind. Our advice, not a Microsoft rule: use the flag for counts and reminders, and use the [Today] view filter when you need the answer to be right at this moment.

For a reminder message built on the same idea, see "Send a reminder before a due date in a SharePoint list".

Option 4. Column formatting with @now

Column formatting can read the current date. Microsoft's column formatting page says @now "resolves to the current date/time, evaluated when the user loads the list view." It also says: "The column formatting doesn't change the data in the list item or file; it only changes how it's displayed to users who browse the list." So it can color a row or show a number, but you cannot filter or count by it.

To open it, open the drop-down menu under a column heading, then under Column Settings choose Format this column. Microsoft's steps are: "To preview the formatting, select Preview. To commit your changes, select Save."

Microsoft's own example colors the current field red when the due date has passed:

=if([$DueDate] <= @now, '#ff0000', '')

Microsoft says the field name in that pattern is the internal name, and a space in a name is written _x0020_, so "Due Date" becomes $Due_x0020_Date. It also says references to other fields work only if they are included in the same view. For more row coloring, see "Turn overdue rows red in a SharePoint list".

Days until due, worked example

You want "today minus date" as a number. A calculated column cannot do it, so use column formatting on the Due date column and show a number in its place.

Microsoft's page says you can do date math: "You can add milliseconds to any date and the result will be a new date." It gives 86,400,000 as the milliseconds in one day. Its reference page shows Number() turning a date into milliseconds and ceiling() rounding up to the next whole number.

Our advice, not a Microsoft rule: combine those pieces like this on the Due date column.

  1. Open Format this column on the Due date column.
  2. Enter JSON with elmType div and this txtContent: =ceiling((Number([$Due_x0020_date]) - Number(@now)) / 86400000)
  3. Select Preview and compare one item against a hand count of days.
  4. Select Save only when the numbers match.

Say an item is due November 20 and today is November 14. The preview should show 6. @now includes the time of day, so rounding up keeps the count from coming out one day short. 0 means due today, and a negative number means the date has passed. Our advice, not a Microsoft rule: if the preview shows NaN, the internal column name is wrong, so check it in the column settings.

DATEDIF between two stored dates

DATEDIF works in a calculated column when both dates are stored in the row. Microsoft's page of formula examples gives: =DATEDIF([Column1], [Column2],"d") and says it "Returns the number of days between the two dates". Use two columns such as Created and Due date. Set the return type to Number.

A formula cannot use today as its second date, because TODAY is not allowed in a calculated column. To measure "days since added", add a Date column with a default value of today, then run DATEDIF between that column and the Due date. For the full formula list, see "SharePoint calculated column formulas for due dates and status".

A flow that refreshes a Days left number column

A scheduled flow can write the live number into a column, so you can sort, filter and count by it.

  1. Add a Number column named "Days left" to the list.
  2. In Power Automate at https://make.powerautomate.com, create a scheduled flow that runs once a day.
  3. Add Get items with Filter Query set to Closed eq 0 and Due_x0020_date ne null, Top Count raised as needed, and Pagination on. Our advice, not a Microsoft rule: the null test keeps a row with no due date from failing the expression in step 5.
  4. Add Apply to each over the Get items value, then an Update item action.
  5. In Days left, enter this expression: div(sub(ticks(formatDateTime(items('Apply_to_each')?['Due_x0020_date'],'yyyy-MM-dd')), ticks(formatDateTime(utcNow(),'yyyy-MM-dd'))), 864000000000)
  6. Run it once and check three rows, including one with a past due date.

Microsoft's workflow functions page says ticks returns the number of ticks, "which are 100-nanosecond intervals". One day is 864,000,000,000 ticks, so the expression gives whole days: 0 for due today, a negative number once the date has passed. Our advice, not a Microsoft rule: check a row due today and a row with a past date before you trust the column.

Which one should you pick

What to avoid

Honest limits

This guide covers lists in Microsoft 365. Menu names move around, so the steps say "exact names may differ". A flag column is only as current as the last flow run. Check with your admin whether your plan includes Power Automate for lists. If you want a related sizing idea, see "Replace a spreadsheet tracker with a SharePoint list".

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

Can I use NOW instead of TODAY?

No. Microsoft's formulas page lists RAND and NOW among the functions that are not supported in lists and libraries.

How do I do today minus a date in a SharePoint calculated column?

You cannot, because TODAY is not allowed in a calculated column. Use column formatting with @now to show the days on screen, or a daily flow that writes the number into a Days left column.

Does DATEDIF work in a SharePoint calculated column?

Yes, between two stored date columns. Microsoft's example is =DATEDIF([Column1], [Column2],"d"), which returns the days between them. It cannot use today as one of the dates.

Why is my SharePoint calculated column not updating with today's date?

A calculated column is worked out from the values in its row and cannot read today's date. Microsoft says a default value fills the column when an item is created. Our advice, not a Microsoft rule: use a [Today] view filter, @now formatting or a flow for anything that must change with the date.

How do I use the current year in a SharePoint calculated column?

Microsoft's examples use YEAR([Column1]) on a stored date. Add a Date column with a default value of today, then use YEAR of that column. YEAR(TODAY()) is not allowed in a calculated column.

Sources