How to Use Conditional Formatting for Dates in the Previous Two Weeks in Excel
Question details
The user needs a way to automatically highlight cells containing dates that occurred within the past 14 days.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking recent deadlines, submissions, or events by dynamically identifying dates from the previous fortnight in a dataset.
- Observed behavior
- Excel does not offer a built-in default option for 'previous two weeks' in its conditional formatting date presets, requiring the use of a custom formula.
Verify that your data column contains valid dates rather than text strings, as the TODAY function relies on standard date serial numbers to accurately calculate the 14-day window.
Use a Custom TODAY() Formula to Highlight the Previous 14 Days
Since Excel lacks a native 'last two weeks' preset, you can create a custom conditional formatting rule using the TODAY and AND functions to precisely identify the required dates.
The AND function checks multiple conditions simultaneously. By combining it with TODAY(), you can set a rolling 14-day window that automatically updates whenever you open or recalculate the spreadsheet.
It is crucial that the cell reference used in your formula exactly matches the active cell in your current selection to prevent formatting alignment errors.
Highlight the range of cells containing your dates (for example, D2:D100). Ensure that the top cell (D2) is your active cell.
Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.
Choose the option 'Use a formula to determine which cells to format' from the list of rule types.
In the formula input box, type: =AND(D2>=TODAY()-14,D2<=TODAY())
Click the Format button, select your preferred fill color or font style to highlight the cells, and click OK twice to apply the rule.
Easily Manage Conditional Formatting with WPS Spreadsheet
WPS Spreadsheet seamlessly handles custom formulas for conditional formatting, allowing you to track dates and deadlines effectively. It provides an intuitive interface that is fully compatible with advanced Excel functions.
- 1. Select Your Dates: Open your file in WPS Spreadsheet and select the date range you want to format.
- 2. Access Conditional Formatting: Go to the Home tab, click on Conditional Formatting, and choose New Rule.
- 3. Input the Formula: Select 'Use a formula to determine which cells to format' and enter =AND(D2>=TODAY()-14,D2<=TODAY()).
- 4. Format and Save: Click Format to choose a highlight color, then click OK to save and apply your new rule.
Frequently Asked Questions
How do I format dates for the NEXT two weeks instead?
You can easily modify the formula to look forward instead of backward. Use the formula =AND(D2>TODAY(),D2<=TODAY()+14) in the conditional formatting rules manager.
Why is my conditional formatting highlighting the wrong cells?
This usually happens if the cell reference in your formula doesn't match the active cell of your initial selection. If you selected D2:D100 but the active cell during setup was D5, a formula using D2 will shift all formatting rules out of alignment. Always ensure the formula references the first selected cell.
Can I highlight dates older than two weeks?
Yes, if you want to highlight overdue dates that occurred more than 14 days ago, simply use the formula =D2<TODAY()-14 and apply your desired formatting.
