Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer
Use FILTER with a real date in H1 and the Open status criterion. At the fixed practice date of 26 October 2026, only T-101 and T-104 qualify.
Before you start
Use the fictional task-deadlines-practice.csv and work in a disposable copy. Compare your result with the independent answer-check.md. Use only fictional data.
Step 1: Show Only 2 Overdue Open Tasks
Our fictional tracker has six tasks, including one due today, one closed task with a past date, and one open task with no date. A red highlight alone can be easy to miss. We will build a separate FILTER result that shows only tasks that are both overdue and still Open, then compare IDs against an independent answer.
Step 2: Set a reproducible as-of date
Paste the practice CSV into a disposable Google Sheet. Put the actual date October twenty-sixth, twenty twenty-six into H1, rather than typing a text label. Check that due dates in column C are date values; ISNUMBER on C2 should return TRUE. The fixed H1 makes this exercise reproducible even when you watch the lesson on another day.
Step 3: Translate the business rule
A row qualifies only when its due date is before H1, its status equals Open, and the date cell is not blank. The due-today row must stay out because before means strictly less than, not less than or equal. A closed past-due row also stays out. Read these conditions before copying the formula, so a plausible-looking output cannot hide the wrong logic.
Step 4: Enter FILTER in a clear range
Choose an empty result area and enter the exact formula shown. Google Sheets FILTER returns rows meeting all supplied conditions. The row conditions have the same six-row height as the A2:D7 source range, which avoids a dimension mismatch. If the formula spills over existing cells, move it or clear only the disposable output area.
=FILTER(A2:D7,C2:C7<$H$1,D2:D7="Open",C2:C7<>"")Step 5: Verify both IDs and near misses
The result should contain T-101 followed by T-104 and no other rows. Check that T-102 is excluded because it is Closed, T-103 because its deadline is today rather than earlier, and T-105 because it has no deadline. This near-miss check catches errors that a simple count of two would not reveal.
Step 6: T-101 and T-104 Are the Only Matches
For a live tracker, H1 can contain TODAY so the view changes when the spreadsheet recalculates. Google notes that TODAY is volatile and reflects the last recalculation, so the answer can change with time. Keep the fixed date for practice and document the reporting time zone in a real team workflow. If no rows match, FILTER may return an error instead of a blank table.
Check your result
Set H1 to the real date 26 October 2026. Assuming A2:D7 holds the six records and C contains real dates, enter =FILTER(A2:D7,C2:C7<$H$1,D2:D7="Open",C2:C7<>"") in an empty output area. The only matching IDs are T-101 (20 October, Open) and T-104 (18 October, Open), in source order. T-103 is due on the as-of date and is not overdue; T-105 has no date; T-106 is Waiting.
Limits and troubleshooting
Column C must contain actual date values, not text that merely looks like a date. Check =ISNUMBER(C2) before comparing. H1 is a fixed demonstration date of 26 October 2026; replace it with =TODAY() only when you want a changing result. FILTER returns #N/A when nothing matches. Do not treat an empty due date as overdue.
Product references
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.