How to Automatically Highlight Past and Expiring Dates in Google Sheets

Managing project milestones, subscription renewals, invoice deadlines, or inventory expiry dates in Google Sheets can quickly become overwhelming without clear visual cues. Scanning dense tables of numbers and dates to find items requiring immediate attention wastes valuable time and increases the risk of missed deadlines.

Google Sheets offers a versatile Conditional Formatting engine that can automatically alter the background fill or text colour of cells based on real-time chronological rules. By combining dynamic functions like TODAY() with formula-based formatting criteria, you can design automated alert systems that flag expired dates in red, highlight deadlines due within the next 7 or 14 days in amber, and visually separate completed tasks.

Ensure Your Dates Are Formatted as Valid Date Data Types

Before applying conditional formatting rules, verify that Google Sheets recognises your entries as true numerical dates rather than plain text strings. A common issue occurs when dates are imported from CSV files or entered with non-standard punctuation, causing formulas to fail silently.

1. Select your entire date column by clicking its column header (e.g. column B).
2. Navigate to the top menu and select Format > Number > Date.
3. Inspect the column alignment: by default, valid dates automatically align to the right side of the cell. If an entry remains left-aligned, it is formatted as plain text and must be re-entered or converted using =DATEVALUE().

Method 1: Highlight Past and Overdue Dates Using Built-In Rules

If you only need to highlight dates that have already passed, Google Sheets provides an intuitive, pre-built conditional formatting rule that requires zero formula writing.

1. Highlight your range of dates (for example, B2:B100).
2. Click Format > Conditional formatting in the main toolbar to open the rules pane on the right.
3. Under the Format rules section, open the Format cells if… dropdown and choose Date is before.
4. In the secondary dropdown that appears below it, select Today.
5. Under Formatting style, choose a light red background fill and dark red bold text to indicate an overdue status.
6. Click Done. Every date prior to the current system date will update dynamically each day.

Method 2: Highlight Overdue Dates Using Custom Formulas

While built-in rules work well for simple checks, they apply formatting to blank cells within your range if not carefully scoped. To prevent empty cells from turning red and to enforce strict date validation, a custom formula provides superior reliability.

1. Select your date range (e.g. B2:B100).
2. In the Conditional format rules sidebar, select Custom formula is from the dropdown.
3. Enter the following formula into the input field:

   =AND(ISDATE(B2), B2 < TODAY())

4. Select your desired red alert formatting style and click Done.

The ISDATE(B2) condition ensures that blank cells or text headers are ignored, while B2 < TODAY() flags dates that precede the current day.

Method 3: Highlight Upcoming Expirations Due in the Next 7 or 14 Days

To proactively address upcoming deadlines before they lapse, you can configure a rule that highlights dates falling within an upcoming window (such as the next 7 days). This gives team members sufficient lead time to complete renewals or follow-ups.

1. In the Conditional Formatting panel, click Add another rule.
2. Ensure your range is set to B2:B100.
3. Select Custom formula is.
4. Enter the following logic to capture dates between tomorrow and seven days from now:

   =AND(ISDATE(B2), B2 >= TODAY(), B2 <= TODAY() + 7)

5. Under Formatting style, select a soft amber or yellow fill colour.
6. Click Done.

You can adjust the integer 7 to 14, 30, or any desired notification threshold to suit your administrative cycle.

Method 4: Highlight the Entire Row Based on a Deadline Column

Highlighting just the date cell is helpful, but highlighting the entire table row makes project tracking significantly easier to read across wide datasets. You can accomplish this by anchoring the column reference with a dollar sign ($).

Suppose your table spans columns A through E, and the due date is located in column B:

1. Select the entire data table range: A2:E100.
2. In the Conditional Formatting panel, choose Custom formula is.
3. Enter this anchored formula:

   =AND(ISDATE($B2), $B2 < TODAY())

4. If you maintain a task status in column C and want to exclude tasks marked “Complete”, update the formula accordingly:

   =AND(ISDATE($B2), $B2 < TODAY(), $C2 <> "Complete")

5. Select your highlight formatting and click Done.

Formula Reference for Date Alert Configurations

The table below summarises the most effective formulas for managing date-based conditional formatting workflows:

Alert Condition	Custom Formula (Assuming Column B)	Recommended Styling
Overdue / Past Dates	=AND(ISDATE(B2), B2 < TODAY())	Light Red Fill / Dark Red Text
Due Today	=B2 = TODAY()	Bright Yellow Fill / Bold Text
Expiring Within 7 Days	=AND(ISDATE(B2), B2 > TODAY(), B2 <= TODAY()+7)	Soft Amber Fill / Dark Orange Text
Expiring Within 30 Days	=AND(ISDATE(B2), B2 > TODAY(), B2 <= TODAY()+30)	Light Blue Fill / Slate Text
Entire Row Overdue (Incomplete)	=AND(ISDATE($B2), $B2 < TODAY(), $C2 <> "Done")	Subtle Row-Wide Tint

Managing Rule Priority and Evaluation Order

When stacking multiple rules (such as Overdue, Due Today, and Due Soon), rule hierarchy is critical. Google Sheets evaluates conditional formatting rules from top to bottom, applying the formatting of the first rule whose condition is met. Ensure your Overdue rule sits at the very top of the list, followed by Due Today, and then your Upcoming Window rules. You can easily adjust their precedence by hovering over any rule in the sidebar and dragging the three-dot handle up or down.