SharePoint provides a great way to filter views using inbuilt functions like Today() and Now(). They come very handy when you are required to get current date and time for any comparison or filtering.
These functions have been very popular throughout these years and work well with all SharePoint versions - MOSS, SharePoint 2010, SharePoint 2013, SharePoint 2016, SharePoint 2019 and SharePoint Online (Microsoft Office 365).
⚡️ How to use/set today's current date in calculated column --> Today()
- Open your List/Library.
- Navigate to Columns section.
- Click Create column. You may alternatively edit an existing calculated column.
- For 'The type of information in this column is:' --> select 'Calculated (calculation based on other columns)'.
- In Formula, use Today() for getting current date.
Current date in SharePoint calculated column
⚠️ Note that calculated column values are not updated automatically and only when list item is modified. It is recommended to use Power Automate / FLOW for updating the calculated field value on regular basis.
Few examples using Today()- Today() --> Gets current date
- Today()-Created --> Gets number of days in between Created Date and Current Date
- Today() + 1 --> Adds 1 day to Current Date
- For 'The data type returned from this formula is: ' --> select the format for the return value, Single line of text / Number / Currency / Date and Time / Yes/No.
- Click OK.
- Check your column and verify the calculated value is computed correctly.
⭐ IMPORTANT - You cannot use [Today] in your formula, this will result in error "Calculated columns cannot contain volatile functions like Today and Me."
Refer this link to fix it - How to use [Today] current date time in SharePoint calculated column
⚡️ How to use/set today's current date with time in calculated column --> Now()
This is similar to previous example, but more accurate up to time including minutes.- Open your List/Library.
- Navigate to Columns section.
- Click Create column. You may alternatively edit an existing calculated column.
- For 'The type of information in this column is:' --> select 'Calculated (calculation based on other columns)'.
- In Formula, use Now() for getting current date and time.
Current date time in SharePoint calculated column using Now
⚠️ Note that calculated column values are not updated automatically and only when list item is modified. It is recommended to use Power Automate / FLOW for updating the calculated field value on regular basis.
Few examples using Now()- Now() --> Gets current date + time
- Now()-Created --> Gets number of days (including hours/minutes) in between Created Date and Current Date/Time
- Now() + 1 --> Adds 1 day to Current Date (including time).
- For 'The data type returned from this formula is: ' --> select the format for the return value, Single line of text / Number / Currency / Date and Time / Yes/No.
- Click OK.
- Check your column and verify the calculated value is computed correctly.
⚡️ How to use today's current date to filter SharePoint views --> [Today]
Something which is required very frequently, filtering views to show relevant information to users. Say, displaying approval tasks which are upcoming today.- Open your List/Library.
- Navigate to Views section.
- Open the respective view.
- Navigate to Filter section.
- Select 'Show items only when the following is true:'
- For 'Show the items when column'
- Select the column from the drop down
- Select the condition which should evaluate to true
- Enter the formula to calculate using [Today]
- Click OK.
- Verify the view is filtered as expected.
-
Few examples using [Today]
- DueDate = [Today]
- DueDate <= [Today] AND Resolved=No
- ReminderDate = [Today]-2
- Created = [Today]-5
This does not stop here. Though SharePoint provides you powerful ready functions like [Today] and [Me], you will have to put in extra efforts to fulfill the actual requirement - like adding logic to skip weekends. In many cases, excel formulas may also work with SharePoint calculated fields.
⛏️ Tip - For calculated column fields, the value will not auto update unless the list item is modified. This has been a pain for developers and admins. There could be multiple possibilities to update the field value, choose the best fit for you -
- Power Automate / FLOW to update item (preferred)
- PowerShell script
- Azure Functions
- Logic Apps
- Managed Metadata error - The data returned from the tagging UI was not formatted correctly
- How to generate client id and secret to register SharePoint App with OAuth
- Create SharePoint Site Collection with new Content database in existing web application
- Fix Error 2711 SQL RBS client - The installer has encountered an unexpected error. The specified Feature name ('Docs') not found in Feature table
- How to exclude results from SharePoint Search
- 'Edit Document' Requires a Windows Sharepoint Services-compatible application and Microsoft Internet Explorer 6.0 or higher
- [Solved] SharePoint Access Denied error editing Document Name
- Recommended size and resolution for SharePoint Online Site logo
- SharePoint Server 2016 IT Preview - new improved Features and Enhancements
- Restore deleted Office 365 SharePoint group site
- SharePoint An unexpected error has occurred - Correlation ID and PowerShell Merge-SPlogfile
- [Solved] SharePoint Search Internal server error exception
- SharePoint CAML query error - The XML source is not correct
- How to hide quick launch in SharePoint classic site
- Not receiving email notification alert in SharePoint Online workflow - Power Automate, FLOW
- Change SharePoint search results FullTextSqlQuery RowLimit 10000
- Fix Power BI error Access to the resource is forbidden when connecting SharePoint Online List as data source
- [Fix] Restricted View permission level missing in SharePoint Online site library
- How to upload file programmatically to SharePoint Document Library using Server Object Model C# .Net
- How to create classic site in SharePoint Online
- That did'nt work, Issue type User not in directory - SharePoint external access error
- Merge-SPlogfile PowerShell - SharePoint Correlation ID error
- Fix Power BI 404 not found error when connecting SharePoint Online List as Data Source
- SharePoint Server 2016 IT Preview Deprecated Removed features
- SharePoint error - An exception occurred when trying to issue security token: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms..
- Java 8: Steam map with Code Examples - Java
- How to extend retiring SharePoint 2010 Workflows and continue with Office 365 - SharePoint
- How to make a div tag clickable - Html
- [Maven] Multiple annotations found at this line pom.xml CoreException, ArtifactResolutionException - Java
- ASCII to HEX and HEX to ASCII Conversion Notepad++ - NotepadPlusPlus
- Facebook Down Will Be Back Soon - Facebook
- How to add ruler in Sublime Text tab window - Sublime-Text
- Detect Data roaming in Android Application - Android