Add a status column beside your project dates, then use a formula to label each row Due Soon, Due, or Overdue. This Numbers for Mac workflow uses a seven-day window, handles hidden times, and leaves missing dates unlabelled.

The instructions follow Apple’s current Numbers User Guide for Mac, which identifies Numbers 15.4. The example is a simple project schedule with one task per row and an editable due date.

Define exactly when each label applies

For this schedule, Due means due today, and Due Soon means due during the next seven calendar days. These are chosen project rules; you can change the advance window to suit your work.

Due date Status
Before today Overdue
Any time today Due
Tomorrow through the seventh day after today Due Soon
Eight or more days after today Later
Empty cell Blank status
Non-date content Check date

Keeping Later avoids labelling every future task as urgent. The formula assesses dates only: it assumes the rows represent unfinished work. If you retain completed tasks, keep them outside this active schedule or add a completion condition before the date tests.

The calendar-day definition matters because Numbers date/time values contain both a date and a time, even when only the date is displayed. A comparison against midnight alone would miss tasks scheduled later today. Apple explains this underlying value structure in Types of arguments and values.

Set up the task and date columns

Use a table with one header row and these three columns:

Column Header Contents
A Task A project task, such as Review shape layout
B Due date The scheduled date
C Status The formula result

Select the body cells in column B. In the Format sidebar, click Cell, open Data Format, and choose Date & Time. Choose a date display that includes the year. For a daily schedule, set the Time menu to None. These controls are documented in Apple’s date and time formatting instructions.

Enter your actual project dates in column B. Numbers interprets typed strings as dates when they use a recognized date format. Check that an ambiguous entry has become the date you intended before relying on its status.

Hiding the time changes its appearance, not the stored value. To inspect an unexpected result, select the date cell and examine the Smart Cell View at the bottom of the window, which shows both the date and time. Apple documents this behavior on the same formatting page.

Enter the status formula

Click cell C2 and enter the following complete formula. It assumes the corresponding due date is in B2 and uses English function names with comma-separated arguments.

=IF(ISBLANK(B2),"",IF(ISDATE(B2),IF(B2<TODAY(),"Overdue",IF(B2<TODAY()+DURATION(0,1),"Due",IF(B2<TODAY()+DURATION(0,8),"Due Soon","Later"))),"Check date"))

Press Return to commit it. Typing an equals sign opens the Formula Editor; Numbers displays the formula’s result in the cell where you entered it. Apple describes entering formulas and comparison operators in Calculate values using data in table cells.

The formula is an application of Apple’s documented functions to the schedule rules above. Its nested IF tests choose one result at a time. Apple explicitly supports placing additional IF functions inside either result argument in its IF reference.

How the formula handles missing and invalid dates

The first test, ISBLANK(B2), returns an empty text result when the due-date cell is completely empty. The next test, ISDATE(B2), allows date comparisons only when the value evaluates to a date; other content receives Check date. See Apple’s ISBLANK reference and ISDATE reference.

A cell containing a space is not empty, even if it looks blank. Similarly, a formula returning an empty string still occupies its cell. For this workflow, clear an unused due-date cell completely; do not type a space to remove its visible contents. Apple’s ISBLANK documentation distinguishes an empty cell from one containing invisible content.

Why the final boundary uses eight days

TODAY() returns today’s date with the time set to midnight. DURATION(0,1) represents zero weeks and one day, so the second date boundary is the start of tomorrow. Apple documents these values in TODAY and DURATION.

The comparisons then work in order: dates before today become Overdue; remaining dates before tomorrow become Due; remaining dates before the start of day eight become Due Soon. Everything beyond that becomes Later.

Using a strict comparison against day eight includes the whole seventh day, including any hidden afternoon or evening time. For a three-day advance window, change only the final DURATION(0,8) to DURATION(0,4). Keep DURATION(0,1) unchanged so that Due still covers today.

Fill the formula down and check the boundaries

Select C2, move the pointer over its border until the yellow autofill handle appears, then drag downward through the intended status cells. Autofill copies formulas and associated formatting and overwrites existing destination content, so restrict the selection to the status cells you want to fill. See Apple’s Autofill cells in Numbers on Mac.

Inspect the next row’s formula: its due-date reference should be B3, followed by B4 in the next row. These references should follow their task rows rather than remain fixed on B2. Apple explains relative and preserved references in Calculate values using data in table cells.

Before using the formula throughout a project, try the following synthetic inputs in a separate practice table with the same three columns. Enter each expression in the indicated column B cell, and fill the status formula through C9. These are expected results derived from the formula’s boundaries.

Row Task in column A Input in column B Expected status
2 Review shape layout =TODAY()-DURATION(0,1) Overdue
3 Count sample blocks =TODAY() Due
4 Check diagram labels =TODAY()+DURATION(0,0,15) Due
5 Arrange project sections =TODAY()+DURATION(0,1) Due Soon
6 Review final geometry =TODAY()+DURATION(0,7,23) Due Soon
7 Prepare next layout =TODAY()+DURATION(0,8) Later
8 Unscheduled task Leave completely empty Blank
9 Task awaiting a date Type Date pending Check date

The two inputs with hour arguments deliberately test hidden times: today at 15:00 must remain Due, and the seventh day at 23:00 must remain Due Soon. Apple’s DURATION syntax places weeks first, days second, and hours third. Use fixed project dates in your working schedule; these relative expressions are practice inputs that move with today.

Add optional highlighting to the status cells

After the labels are correct, add color to column C using text rules:

  1. Select the status body cells.
  2. In the Format sidebar, click Cell, then Conditional Highlighting and Add a Rule.
  3. Choose Text and the equality rule, then enter Overdue and select a fill or text style.
  4. Add separate equality rules for Due and Due Soon.
  5. Click Done when finished.

Use distinct, readable styles and retain the written labels. Apple documents selecting cells, adding conditions, and choosing styles in Use cell conditions in Numbers on Mac.

Apply these rules to the status cells themselves. The documented highlighting mechanism evaluates a cell’s own value and can compare it with another cell’s value; this workflow needs only exact text matches. It does not require a custom formula rule or promise automatic whole-row coloring. If multiple rules match, Numbers uses the first rule in the list. See Apple’s conditional highlighting guide.

Resolve unexpected labels and stale dates

If a result is unexpected, inspect the input before changing the thresholds:

  • Check date appears: verify that the due-date cell contains a recognized date rather than a note or invisible content. ISDATE tests the value, as documented in Apple’s ISDATE reference.
  • A formula error appears: click the error icon to read its message and inspect any referenced input cell. Apple documents this diagnostic step in Calculate values using data in table cells.
  • The labels still reflect yesterday: reopen the file or change a table. Apple says TODAY() updates on those events; do not assume an untouched open document refreshes exactly at midnight. See TODAY.

For a local Mac file, Apple says the file’s time zone matches the Mac’s date and time settings when the file was last saved. Check that context if the apparent day boundary is unexpected. The TODAY reference explains the file time-zone rules.

Once the practice rows produce the expected labels, use the formula with your active project dates and extend it as the schedule grows. Keep the seven-day definition consistent, resolve Check date entries, and let the status text carry the meaning even when highlighting is absent.

Sources