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