Others

How to Highlight Today and Past Dates in Google Sheets

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to create a conditional formatting rule that triggers when a date becomes today, stays highlighted as the date passes, and stops being highlighted if a separate column contains 'YES'.

Product: Google Sheets
Device & OS: not provided
Scenario: Tracking deadlines or pending tasks where overdue items need visual highlighting until they are explicitly marked as completed.
Observed behavior: Standard formatting rules targeting only TODAY() drop the highlight once the date becomes a past date. A more robust rule is required to maintain the highlight for past dates while respecting an exclusion condition.

Before you start

Ensure your date column contains properly formatted date values rather than plain text, and identify the exact column letter that will contain your 'YES' completion status.

Solution 1Recommended

Use a Custom Formula with the AND() Function

Applying a custom formula allows you to simultaneously check if a date is less than or equal to today, and verify that the exclusion column does not say 'YES'.

Instead of relying on standard 'Date is' rules which might only trigger exactly on today's date, a custom formula can evaluate both the past-due condition and the completion status at the same time.

Select your date range

Highlight the cells or rows you want to apply the formatting to (for example, A2:A100).

Open Conditional Formatting

Go to the 'Format' menu at the top of your Google Sheets interface and click on 'Conditional formatting'.

Choose Custom Formula

In the formatting rules sidebar, click the drop-down menu under 'Format rules' and select 'Custom formula is' at the very bottom.

Enter the formula

Input =AND(A2<=TODAY(), B2<>"YES") into the value box. Ensure you replace 'A2' with the top cell of your date column and 'B2' with the top cell of your completion status column.

Apply formatting style

Choose your preferred highlight color under 'Formatting style' and click 'Done' to save and apply the rule.

Highlighting Entire Rows: If you want to highlight the whole row instead of just the date cell, change your 'Apply to range' to cover all columns (e.g., A2:Z100) and add a dollar sign before the column letters in your formula: =AND($A2<=TODAY(), $B2<>"YES").

Solution 2

Use a Helper Column

If you have complex logic or multiple conditions, using a helper column to output a simple TRUE/FALSE value can make your conditional formatting easier to manage.

Manage Spreadsheets with WPS Office

Easily Highlight Dates and Deadlines Using WPS Spreadsheet

WPS Spreadsheet provides robust conditional formatting capabilities perfectly matching your data management needs. You can easily track deadlines, manage tasks, and highlight overdue dates using custom formulas.

1. Select the target data: Open your document in WPS Spreadsheet and highlight the range of cells you want to format.
2. Access Conditional Formatting: Navigate to the 'Home' tab on the top ribbon and click on 'Conditional Formatting'.
3. Create a new rule: Select 'New Rule' from the dropdown menu, then choose 'Use a formula to determine which cells to format'.
4. Apply formula and style: Enter your custom formula such as =AND(A2<=TODAY(), B2<>"YES"), click the 'Format' button to pick a fill color, and click 'OK'.

Fully compatible with Microsoft Excel (.xlsx) formats and Google Sheets exports.Supports advanced conditional formatting and powerful custom functions like TODAY().Intuitive offline access to your spreadsheets with no internet connection required.Free, lightweight, and user-friendly interface for seamless daily work.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This usually happens when the cell reference in your custom formula doesn't match the top-left cell of your 'Apply to range'. For example, if your range starts at A2, your formula must start referencing A2. Also, ensure you are using absolute references (like $A2) correctly if highlighting whole rows.

Why does the TODAY() function not update in my spreadsheet?

The TODAY() function updates dynamically, but you might not see the change immediately if the spreadsheet was left open in a browser tab across multiple days. Refreshing the web page or editing any cell will force the sheet to recalculate.

How do I remove a conditional formatting rule?

Select the cells with the formatting, go to Format > Conditional formatting. In the sidebar that appears on the right, hover over the rule you wish to remove and click the trash can (Delete) icon.

Can I highlight dates that are exactly today but not past dates?

Yes. Instead of using the custom formula mentioned above, you can simply use the formula =AND(A2=TODAY(), B2<>"YES"). Changing the less-than-or-equal-to (<=) operator to an equals sign (=) restricts the highlight to today only.

Office Quick Solutions