Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to highlight upcoming due dates in Excel and Google Sheets
Topic:Conditional formattingExcel & Google Sheets
=AND(A2<TODAY()+7,A2>TODAY())✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Due Date | Task |
| 2023-10-15 | Report |
| 2023-10-20 | Meeting |
| 2023-10-25 | Presentation |
| 2023-10-30 | Review |
=AND(A2<TODAY()+7,A2>TODAY())→FALSE
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the range of cells containing your due dates.
- 2Go to the Home tab and click on Conditional Formatting.
- 3Choose 'New Rule' and select 'Use a formula to determine which cells to format'.
- 4Enter the formula `=AND(A2<TODAY()+7,A2>TODAY())` and set your desired formatting style.
- 5Click OK to apply the rule.
Tips
- Adjust the number of days in the formula to fit your needs (e.g., `TODAY()+14` for two weeks).
- Ensure your date format is consistent across your sheet to avoid errors.
- Remember to update your sheet regularly to keep the highlights accurate.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if my due dates are in a different column?
Simply replace `A2` in the formula with the appropriate cell reference for your column.
Can I highlight past due dates as well?
Yes, use a formula like `=A2<TODAY()` to highlight past due dates.
How do I remove the conditional formatting once it's applied?
Select the formatted cells, go to Conditional Formatting, and choose 'Clear Rules'.
More on Conditional formatting
See all →How-to
How to highlight duplicate values
Highlight duplicate cells automatically with Conditional Formatting using a COUNTIF rule — no formulas to copy manually.
How-toHow to highlight cells greater than a value
Use Conditional Formatting with a simple greater-than rule to automatically color cells that exceed a threshold value.
How-toHow to highlight the highest value in a range
Color the maximum value in a range automatically using a Conditional Formatting rule based on the MAX function.
How-toHow to highlight the lowest value in a range
Color the minimum value in a range using a Conditional Formatting rule built on the MIN function.
How-toHow to highlight blank cells
Use conditional formatting with ISBLANK to automatically highlight empty cells in your spreadsheet for easy visual identification.
How-toHow to highlight cells that contain specific text
Use conditional formatting with SEARCH or ISNUMBER(SEARCH()) to highlight any cell containing a specific word or phrase automatically.