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 microsoftexpert | July 31, 2019
Traditionally, the HR process is paperwork-heavy by nature, and after many, many challenges and observations, Microsoft has finally migrated their HR services to the cloud. Having a cloud-based HR service...
Posted by microsoftexpert | July 25, 2019
We’ve often talked about security and compliance here on the ESW Associates blog. Because security is paramount, it’s only natural Microsoft has been investing in making it easier for organizations...
Posted by microsoft-admin | May 29, 2019
1. Use of Agile/Quick Approach to Development Among development approaches, the most popular ones are the Waterfall and Agile. While Waterfall worked pretty well when computers were a new technology,...