Use Today in Calculated Columns in SharePoint

Use Today in Calculated Columns in SharePoint

Posted by ESW Team | May 7, 2018 | Formulas, Office 365, SharePoint

Use Today in Calculated Columns in SharePoint

Today we are putting to rest the question, and myth, of if you can use today in calculated columns in SharePoint.  This question has been around for over a decade and depending where you go on the internet, the answer is either yes or no.

We can definitively say: No

The proof is below.  There is a known trick of doing the below steps. This link is the #1 search result as of today. We are hoping this insight surpasses it so there is less misinformation on the internet.

  1. Add a text field named Today
  2. Add the Today field as part of the calculated equation
  3. Delete the Today field and the calculated equation works!

We tested this first part out and low and behold it does work.

Status is a calculated column:

List Settings For Using Todays Date as a Calculated Column in SharePoint

Status Equation:

Formula For Using Todays Date as a Calculated Column in SharePoint

Equation Results:

Formula results For Using Todays Date as a Calculated Column in SharePoint

So it works right?

Well, sort of.  It works when it is initially setup.  What the last screenshot doesn’t show is that if you refresh the formulas on the next day or any day thereafter, it doesn’t re-calculate.  If you look at the below screenshot, you will see the calculated status is 4 which was correct on May 5th, but since today is May 7th the formula has not updated, with a screen refresh, and therefore you cannot use today in calculated columns in SharePoint.

Equation Miscalculation:

Formula Results are Wrong For Using Todays Date as a Calculated Column in SharePoint

Want more insights like this? Get notifications of our Office 365 updates!

Another interesting power admin trick for Office 365:
Using Repeating Tables in PowerApps

Related Blogs

Posted by microsoft-admin | February 13, 2019
Office 365 and G Suite (formerly known as Google Apps for Work) are productivity tools for performing tasks in the cloud. Both let you create documents (Google Docs and Word),...
Posted by microsoft-admin | February 4, 2019
Making smarter apps requires using innovative services that facilitate cognitive intelligence, and Azure Cognitive Services is making waves as a forerunner for smart app development. This technology offers a whole...
Posted by microsoft-admin | January 22, 2019
Team collaboration is as important as ever, and there are many players in the game. Today we’ll take a look at two options you can choose from: Microsoft Teams, and...