⛏️ Fix [Today] volatile function error for SharePoint calculated field current date
We trick SharePoint here by creating a dummy column to capture current date and overcome the volatile functions error for calculated column formula. This trick works for all SharePoint versions - MOSS, SharePoint 2010, SharePoint 2013, SharePoint 2016, SharePoint 2019 and SharePoint Online (Microsoft Office 365).- Create a new column called Today (Single line of text).
- Create a calculated column, name it anything you want, say 'CurrentDate'.
- In the formula, insert the column you created previously 'Today'.
- Set the data type to 'Date and Time'.
- 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.
- Now delete the 'Today' column you created in first step. Don't worry, just go ahead.
- Add the 'CurrentDate' column to your view and check the value.
- You should see today's date in there.
- Modify the formula as required, you can use the column Today just like the function [Today] without any issues.
⚡️ 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:
- Use scheduled Powershell script to update field formula every morning. this will refresh your values in column every morning. all you need to do is to update formula to something else and change it back to original. This approach is only useful if you are handling date, without time.
anon 10 Dec 2020 23:01:39 GMT
- No, cannot directly specify today and me in calculated column to set the values, will need to create columns Today and Me, then delete these 2 columns. Delete it! its important!
anon 25 Nov 2020 12:33:20 GMT
- Use JSON column formatting instead, you can compute and show value in the view for any SharePoint field. This should be refreshed daily.
anon 12 Nov 2020 04:54:01 GMT
- You can use JsLink in your view instead for getting current/today date.
anon 07 Nov 2020 00:21:55 GMT
- 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!
More Posts related to SharePoint,
- SharePoint error - An exception occurred when trying to issue security token: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms..
- PowerShell iterate and get all SharePoint sites and sub sites
- SharePoint Online: Editing session has ended Message
- How to Share Microsoft SharePoint Site with Users or Groups
- How to delete SharePoint Online List Item using REST API
- SharePoint List excel import - This table exceeds the maximum number of supported rows
- How to disable SharePoint subsite creation option for owners
- SharePoint 2010 August 2015 Update KB3055049 - Duplicate Document ID issue bug fixed
- SharePoint Server 2016 Preview installation error - This Product Key isn't a valid Microsoft Office 2016 Product Key. Check that you've entered it correctly.
- How to upload file programmatically to SharePoint Document Library using Server Object Model C# .Net
- Access URL for SharePoint Tenant Admin Center (Online Office 365)
- Error when deleting SharePoint Online folder or file
- How to extend retiring SharePoint 2010 Workflows and continue with Office 365
- How to generate client id and secret to register SharePoint App with OAuth
- How to delete SharePoint List Item programmatically using C#.Net
- How to retrieve all SharePoint Online List Items using Rest API
- [Solved] SharePoint Search Internal server error exception
- Create SharePoint Site Collection using PowerShell New-SPSite
- Fix SharePoint PowerShell error - The term Get-SPweb is not recognized as the name of a cmdlet function script file or operable program
- How to enable anonymous public access for SharePoint Online site collection, file, folder without login ?
- SharePoint Server 2016 installation System Hardware requirements
- How to create SharePoint Online List Item using REST API
- Changed AD user display name showing old name in SharePoint
- Recommended size and resolution for SharePoint Online Site logo
- Microsoft 365: How to Turn Off Delve in SharePoint Online for All Users
More Posts:
- Quickly install VS Code on macOS Sonoma/Ventura - MacOS
- Read file from Windows CMD (Command Line) - Windows
- Health Status Page for OpenAI ChatGPT or GPT 4 - HowTos
- Share Image to WhatsApp with Caption from your Android App - WhatsApp
- PowerShell Alias Type Commands List for Mac - MacOS
- Get the Complete Sha256 Container ID for Docker Run Command - Docker
- Tesla hit by a complete network and mobile app outage - 23 Sept 2020 11am ET (US and Europe) - News
- Java: Convert Byte to Binary String Example - Java