Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to highlight overdue dates in Excel and Google Sheets
Topic:Date arithmeticExcel & Google Sheets
=A2<TODAY()✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Due Date | Task | Status |
| 2024-01-01 | File report | Done |
| 2024-02-15 | Send invoice | Pending |
| 2024-03-20 | Review contract | Open |
| 2025-12-31 | Year-end audit | Scheduled |
=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 date cells you want to monitor (e.g., A2:A6).
- 2In Excel: go to Home > Conditional Formatting > New Rule > 'Use a formula to determine which cells to format'.
- 3In Google Sheets: go to Format > Conditional Formatting, choose 'Custom formula is'.
- 4Enter the formula =A2<TODAY() (use the top-left cell of your selection).
- 5Choose a red fill or font color, then click OK or Done. Dates before today will be highlighted immediately.
Tips
- Add a second conditional formatting rule for dates within the next 7 days using =AND(A2>=TODAY(),A2<=TODAY()+7) and apply a yellow/orange fill to create an 'almost due' warning tier.
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
Why is the entire column highlighted instead of just past-due rows?
Make sure to lock the row reference in the formula if needed, and ensure the formula reference (A2) matches the top-left cell of your selected range. In most cases =A2<TODAY() with a relative reference is correct.
Can I highlight the entire row when a date in column A is overdue?
Yes. Select the full data range (e.g., A2:D6), then use the formula =$A2<TODAY(). The $ locks column A so every column in the row checks the date in column A.
More on Date arithmetic
See all →Reference
TODAY / DATEDIF
Work with the current date and measure the gap between two dates.
How-toHow to calculate age from a date of birth
Turn a birth date into an age in years with DATEDIF and TODAY. Works the same in Excel and Google Sheets.
ReferenceDATEDIF
Calculates the difference between two dates in years, months, or days using a unit code string.
How-toHow to calculate days between two dates
Subtract one date from another to get the number of days, or count working days with NETWORKDAYS. Works in both apps.
How-toHow to add months to a date
Shift a date forward or back by a number of months with EDATE — it handles month lengths correctly. Works in both apps.
How-toHow to flag overdue dates
Mark rows whose due date has passed by comparing it to TODAY() with IF. Works the same in Excel and Google Sheets.