SharePoint formula - Calculated columns cannot contain volatile functions like Today and Me


If you try to create a calculated column in SharePoint which contains [Today] or [Me] functions, you will be greeted with below error.

Sorry, something went wrong

Calculated columns cannot contain volatile functions like Today and Me.

Best approach to fix this and use Today's/Current Date in formula

We trick SharePoint here by creating a dummy column to capture current date. This trick works for all SharePoint versions - MOSS, SharePoint 2010, SharePoint 2013, SharePoint 2016, SharePoint 2019 and SharePoint Online (Microsoft Office 365).
  1. Create a new column called Today (Single line of text).
  2. Create a calculated column, name it anything you want, say 'CurrentDate'.
  3. In the formula, insert the column you created previously 'Today'.
  4. Set the data type to 'Date and Time'.
  5. Set the Date and Time format to 'Date Only'. You can even select Date & Time if you would want to use hours/minutes as well.
  6. Now delete the 'Today' column you created in first step. Don't worry, just go ahead.
  7. Add the 'CurrentDate' column to your view and check the value.
  8. You should see today's date in there.
  9. Modify the formula as required, you can use the column Today just like the function [Today] without any issues.
You can use similar logic to use Me (current logged in SharePoint user name) in your formula.

Important - Calculated columns by design do not update the values automatically, the value updates only when the list item is modified/updated. So if you go back to the view at a later point, you will still see the old date (the day when you set the formula) being referred in the formula.


We urge you to use Power Automate / FLOW to update the list item values instead of using calculated columns if you want the values to update on regular basis.


Comments:

  • Thanks for this post. Now I know why my date does not update daily. Know this approach, but never knew about the auto update issue. Good learning for me, keep it up!
    anon 10 Sep 2020 23:19:05 GMT
  • I remember using this trick for MOSS during my early days with SharePoint. Still works with SharePoint Online in Office 365.
    anon 23 July 2020 21:13:50 GMT
  • This old school trick still works like a charm.
    anon 12 June 2020 09:23:11 GMT
  • Further comments disabled!