Microsoft Lists Formula to Check Items Submitted in the Last 7 Days
Question details
The user needs a calculated formula to determine if an item's submission date falls within the past seven days.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Tracking recent list submissions by dynamically comparing a recorded date column against the current date.
- Observed behavior
- The user wants a true/false output to easily filter or identify items submitted within the previous 7-day rolling window.
Verify that your target column (e.g., 'Request Submitted') is formatted as a 'Date and Time' data type in your list settings to ensure the formula calculates correctly.
Use the IF and TODAY Functions in a Calculated Column
Create a calculated column that evaluates the submission date against the TODAY() function minus 7 days.
Microsoft Lists and SharePoint share a calculation syntax that is highly similar to Microsoft Excel. By subtracting 7 from the TODAY() function, you can establish a dynamic 7-day threshold. The IF function then outputs 'True' if the condition is met and 'False' if it is not.
Open your Microsoft List. Click the gear icon in the top right corner and select 'List settings'. Alternatively, click '+ Add column' directly in the list view and choose 'Calculated'.
Name your new column (for example, 'Recent Submission'). Under the column types, ensure 'Calculated (calculation based on other columns)' is selected.
In the Formula box, enter the following exact string: =IF([Request Submitted]>=TODAY()-7,"True","False"). Be sure to replace '[Request Submitted]' with the exact name of your date column.
Under 'The data type returned from this formula is', select 'Single line of text' or 'Yes/No'. Click 'OK' at the bottom of the page to save your new column.
Track Data and Manage Lists Efficiently with WPS Office
If you frequently work with data tracking, list exports, and complex formulas, WPS Office offers a lightweight, highly compatible alternative to the Microsoft ecosystem. WPS Spreadsheet seamlessly handles advanced date formulas, conditional formatting, and data validation to help you manage your workflows effectively—completely free.
- 1. Install WPS Office: Download and install WPS Office for free from the official website to your preferred device.
- 2. Export List Data: In Microsoft Lists, click 'Export' and choose 'Export to Excel' or 'Export to CSV' to download your current data.
- 3. Analyze in WPS Spreadsheet: Open the exported file in WPS Spreadsheet to seamlessly continue filtering, analyzing, and applying familiar date formulas to your data.
Frequently Asked Questions
Why is my Microsoft Lists formula returning a syntax error?
Syntax errors usually occur if the column name referenced in your formula does not exactly match the actual column name in your list. Ensure you use exact spelling and include square brackets around names with spaces (e.g., [Request Submitted]). Additionally, depending on your regional settings, you may need to use semicolons (;) instead of commas (,) to separate formula arguments.
Can I use view filtering instead of creating a calculated column?
Yes. If you simply want to view items from the last 7 days without creating a new column, you can edit the list view. Go to View settings > Edit current view, scroll down to 'Filter', and set the rule to show items only when your date column is 'greater than or equal to' [Today]-7.
Does the TODAY() function in a calculated column update automatically every day?
In Microsoft Lists and SharePoint, a calculated column using the TODAY() function only recalculates when the specific item is created or modified. If an item is left untouched, the formula value will become outdated. For truly dynamic daily updates, it is recommended to use View Filters or JSON Column Formatting rather than a calculated column.
