Track overdue tasks

What you will build

Create a Flag column that automatically marks tasks whose due date has passed and whose status is not complete, while ignoring blank due dates. The lesson uses fictional data so every step can be reproduced safely.

Learning objective

Use ARRAYFORMULA, IF, and TODAY to evaluate multiple rows from one anchor cell, understand how the condition arrays align row by row, and verify the result when a status changes.

Prepare the sample data

Open the exact source workbook below or make your own copy. The Example tab contains the Task, Due, and Status table. Keep the source workbook unchanged and do the exercise on your copy.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The practical-formulas-08 source workbook in Google Sheets with the Example tab selected and the full Task, Due, and Status table visible.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: On your copy, open Example. Confirm B2:B4 are real date values and C2:C4 use the statuses Done, Working, and Planned. Enter Flag in D1.

The _result workbook before the formula is entered, showing Task, Due, Status, and the Flag header in D1.

Step 2: Select D2 and enter the formula below once. This is a spilling array formula: its results expand through D2:D4 automatically, so do not drag the fill handle or copy the formula down.

Formula to try

In Example!D2, enter the formula below and keep D2:D4 clear beforehand so the spill range is not blocked.

=ARRAYFORMULA(IF((B2:B4<>"")*(B2:B4<TODAY())*(C2:C4<>"Done"),"Overdue",""))

The _result workbook with Example!D2 selected; the formula bar shows the complete ARRAYFORMULA and the spill result is blank for Brief, Overdue for Design, and Overdue for QA.

How this formula works=ARRAYFORMULA(IF((B2:B4<>"")*(B2:B4<TODAY())*(C2:C4<>"Done"),"Overdue",""))

Overall meaning of the whole formula
- Automatically evaluate rows 2–4; if a row has a due date, that date is earlier than today, and the status is not "Done", return "Overdue" for that row; otherwise leave it blank.
ARRAYFORMULA(array_formula)
- array_formula is required; it accepts an expression that can produce one or more values. Here it is IF((B2:B4<>"")*(B2:B4<TODAY())*(C2:C4<>"Done"),"Overdue","").
- ARRAYFORMULA lets the IF expression evaluate rows 2–4 together and return a three-item array from anchor cell D2.
- Because the result spills from D2 through D4, there is no fill-handle or copy-down step.
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression is required; here it is the product of three condition arrays: (B2:B4<>"")*(B2:B4<TODAY())*(C2:C4<>"Done").
- value_if_true is required; this lesson passes "Overdue" when all three row-level tests are true.
- value_if_false is optional; this lesson passes "" so a row displays blank when at least one test is false.
TODAY()
- TODAY takes no parameters; its parentheses stay empty.
- It returns the spreadsheet current date at calculation time. B2:B4<TODAY() compares each Due value with that date.
Condition arrays and multiplication
- B2:B4<>"" checks that Due is not blank and evaluates to {TRUE; TRUE; TRUE} for the sample.
- At the 2026-10-05 audit, B2:B4<TODAY() evaluates to {TRUE; TRUE; TRUE} because all three due dates are earlier than the current date.
- C2:C4<>"Done" evaluates to {FALSE; TRUE; TRUE} because Brief is Done while Design and QA are not.
- The arrays are all three rows tall, so their elements align by row. Multiplication coerces TRUE/FALSE to 1/0 and produces {0; 1; 1}; any zero makes that row false for IF.
Whole-expression evaluation
- Row 2 — Brief: the due date exists and is past, but Status is Done, so the condition product is 0 and IF returns an empty string.
- Row 3 — Design: the due date exists, is past, and Status is Working, so the condition product is 1 and IF returns Overdue.
- Row 4 — QA: the due date exists, is past, and Status is Planned, so the condition product is 1 and IF returns Overdue.
Why the displayed final result appears
- The output array is blank, Overdue, Overdue from D2 through D4.
- During the audit, changing C3 from Working to Done made D3 blank; restoring C3 to Working made D3 return to Overdue.
- This confirms that the displayed output is calculated from the source data and one formula entered only in D2.

Checkpoint

Task	Due	Status	Flag
Brief	2026-09-15	Done
Design	2026-09-20	Working	Overdue
QA	2026-10-01	Planned	Overdue

Expected result at the 2026-10-05 audit: Brief has no flag because it is Done; Design and QA display Overdue.

Common mistakes and fixes

If a task is not flagged as expected, confirm Due is a real date value and that the completed status is spelled exactly Done. TODAY uses the spreadsheet current date, so the result also depends on calculation time.

Independent practice

Temporarily change Design from Working to Done and confirm D3 becomes blank; then restore Working and confirm D3 returns to Overdue.

Key takeaways

✓Use one ARRAYFORMULA in D2 to calculate overdue flags for multiple rows.
✓The conditions require a nonblank Due, a Due earlier than TODAY, and a Status other than Done.
✓Brief is not flagged because it is Done; Design and QA are Overdue with the current sample data.
✓The B2:B4 and C2:C4 arrays must have compatible dimensions so elements align by row.
✓Do not drag or copy the formula into D3:D4 because the result already spills from D2.

Continue learning

Previous lesson: Calculate working days. Next lesson: Compare two lists.

What you will build

Learning objective

Use ARRAYFORMULA, IF, and TODAY to evaluate multiple rows from one anchor cell, understand how the condition arrays align row by row, and verify the result when a status changes.

Prepare the sample data

Open the exact source workbook below or make your own copy. The Example tab contains the Task, Due, and Status table. Keep the source workbook unchanged and do the exercise on your copy.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: On your copy, open Example. Confirm B2:B4 are real date values and C2:C4 use the statuses Done, Working, and Planned. Enter Flag in D1.

Step 2: Select D2 and enter the formula below once. This is a spilling array formula: its results expand through D2:D4 automatically, so do not drag the fill handle or copy the formula down.

Formula to try

In Example!D2, enter the formula below and keep D2:D4 clear beforehand so the spill range is not blocked.

=ARRAYFORMULA(IF((B2:B4<>"")*(B2:B4<TODAY())*(C2:C4<>"Done"),"Overdue",""))

How this formula works=ARRAYFORMULA(IF((B2:B4<>"")*(B2:B4<TODAY())*(C2:C4<>"Done"),"Overdue",""))

Overall meaning of the whole formula
- Automatically evaluate rows 2–4; if a row has a due date, that date is earlier than today, and the status is not "Done", return "Overdue" for that row; otherwise leave it blank.
ARRAYFORMULA(array_formula)
- array_formula is required; it accepts an expression that can produce one or more values. Here it is IF((B2:B4<>"")*(B2:B4<TODAY())*(C2:C4<>"Done"),"Overdue","").
- ARRAYFORMULA lets the IF expression evaluate rows 2–4 together and return a three-item array from anchor cell D2.
- Because the result spills from D2 through D4, there is no fill-handle or copy-down step.
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression is required; here it is the product of three condition arrays: (B2:B4<>"")*(B2:B4<TODAY())*(C2:C4<>"Done").
- value_if_true is required; this lesson passes "Overdue" when all three row-level tests are true.
- value_if_false is optional; this lesson passes "" so a row displays blank when at least one test is false.
TODAY()
- TODAY takes no parameters; its parentheses stay empty.
- It returns the spreadsheet current date at calculation time. B2:B4<TODAY() compares each Due value with that date.
Condition arrays and multiplication
- B2:B4<>"" checks that Due is not blank and evaluates to {TRUE; TRUE; TRUE} for the sample.
- At the 2026-10-05 audit, B2:B4<TODAY() evaluates to {TRUE; TRUE; TRUE} because all three due dates are earlier than the current date.
- C2:C4<>"Done" evaluates to {FALSE; TRUE; TRUE} because Brief is Done while Design and QA are not.
- The arrays are all three rows tall, so their elements align by row. Multiplication coerces TRUE/FALSE to 1/0 and produces {0; 1; 1}; any zero makes that row false for IF.
Whole-expression evaluation
- Row 2 — Brief: the due date exists and is past, but Status is Done, so the condition product is 0 and IF returns an empty string.
- Row 3 — Design: the due date exists, is past, and Status is Working, so the condition product is 1 and IF returns Overdue.
- Row 4 — QA: the due date exists, is past, and Status is Planned, so the condition product is 1 and IF returns Overdue.
Why the displayed final result appears
- The output array is blank, Overdue, Overdue from D2 through D4.
- During the audit, changing C3 from Working to Done made D3 blank; restoring C3 to Working made D3 return to Overdue.
- This confirms that the displayed output is calculated from the source data and one formula entered only in D2.

Checkpoint

Task	Due	Status	Flag
Brief	2026-09-15	Done
Design	2026-09-20	Working	Overdue
QA	2026-10-01	Planned	Overdue

Expected result at the 2026-10-05 audit: Brief has no flag because it is Done; Design and QA display Overdue.

Common mistakes and fixes

Independent practice

Temporarily change Design from Working to Done and confirm D3 becomes blank; then restore Working and confirm D3 returns to Overdue.

Key takeaways

✓Use one ARRAYFORMULA in D2 to calculate overdue flags for multiple rows.
✓The conditions require a nonblank Due, a Due earlier than TODAY, and a Status other than Done.
✓Brief is not flagged because it is Done; Design and QA are Overdue with the current sample data.
✓The B2:B4 and C2:C4 arrays must have compatible dimensions so elements align by row.
✓Do not drag or copy the formula into D3:D4 because the result already spills from D2.

Continue learning

Previous lesson: Calculate working days. Next lesson: Compare two lists.

Track overdue tasks

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Track overdue tasks

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments