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

Office 2016 vs Office 365
Posted by sharepointchamp | May 10, 2018
Office 2016 vs Office 365 Microsoft’s Office Suite is a staple of the workplace and home. Office 2016 is the “offline” Office version where you install the applications on your...
Posted by markertingadmin | April 6, 2018
GDPR Compliance in Office 365 The EU’s General Data Protection Regulation (GDPR) will be in full effect come May 25th 2018, and it brings several changes to data protection for...
Posted by russell | April 4, 2018
March 2018 Office 365 Updates We’re Excited About March was a busy month for Office 365. There were 33 new additions to the Office 365 roadmap with a heavy focus on...