How to Highlight Excel Dates Expiring Within 21 Days
Question details
The user needs a method to automatically highlight authorization end dates that fall between today and exactly 21 days from the current date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking upcoming deadlines or authorization expiration dates in a spreadsheet.
- Observed behavior
- Dates falling within a rolling 21-day window need to be visually distinguished using a custom conditional formatting rule.
Ensure that the cells in your date column are formatted as actual Excel dates rather than plain text, as date-based formulas rely on standard date formatting to calculate intervals correctly.
Use Conditional Formatting with the AND and TODAY Formula
Applying a custom formula rule in Conditional Formatting allows you to precisely target dates falling within a rolling 21-day window.
By utilizing the TODAY() function within an AND() statement, Excel will automatically evaluate if a specific date is greater than or equal to today, and less than 22 days in the future. This creates a dynamic 21-day expiration warning that updates automatically every day.
Highlight the range of cells containing your authorization end dates. For example, click and drag from cell B2 down to your last date.
Go to the 'Home' tab on the Excel ribbon. Click 'Conditional Formatting' in the Styles group, and select 'New Rule' from the dropdown menu.
In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format' from the list of rule types.
Type the formula =AND(B2>=TODAY(),B2<TODAY()+22) into the formula bar. Replace 'B2' with the very first cell of your highlighted range.
Click the 'Format' button, choose a noticeable fill color (like yellow or red) from the Fill tab, click 'OK', and then 'OK' again to apply the rule.
Highlight Expiring Dates Easily in WPS Spreadsheet
WPS Spreadsheet provides robust conditional formatting capabilities and full compatibility with standard spreadsheet formulas like TODAY() and AND(). You can easily track upcoming deadlines and expiring dates in a clean, intuitive interface.
- 1. Open your spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
- 2. Select the date range: Highlight the column or cells containing the expiration dates you want to track.
- 3. Access conditional formatting: Navigate to the Home tab on the top ribbon, click on Conditional Formatting, and select New Rule.
- 4. Apply the custom formula: Choose the option to use a formula, enter =AND(B2>=TODAY(),B2<TODAY()+22), select your desired highlight color, and click OK.
Frequently Asked Questions
Why is the conditional formatting highlighting empty cells?
Empty cells can sometimes be evaluated as a zero value in Excel date calculations. To prevent empty cells from being highlighted, update your formula to include a check for blanks: =AND(B2<>"", B2>=TODAY(), B2<TODAY()+22).
How can I highlight dates that have already passed?
You can create an additional conditional formatting rule using a simpler formula. Select your range, go to Conditional Formatting > New Rule, and use the formula =B2<TODAY() to highlight dates that are already expired.
What if my dates are not highlighting correctly despite using the right formula?
This usually happens when dates are accidentally stored as text. Select your date column, go to the Data tab, and use 'Text to Columns' to convert them into proper date formats. Also, ensure your formula references the top-left cell of your selection as a relative reference (e.g., B2) rather than an absolute reference (like $B$2).
