LibreOffice / Nextcloud Office – "Due Tasks" Conditional Formatting

LibreOffice Calc – "Due Tasks" Conditional Formatting

Highlight whole rows in red when a task is due and must be acted on.

---

Goal and Logic

Core Conditions

For a row to be considered due, two things must be true:

Target Date condition

The Target Date in column E must be today or earlier. Past dates have smaller numeric values than today, so the check is: TODAY() >= TargetDate

Is It Due flag

Column F (IS IT DUE) must evaluate to TRUE. Typically this is a formula in F2 like =TODAY() >= E2, which returns TRUE when the target date is today or in the past.

Target Date must exist

Column E must contain a real date value — not text and not blank — so empty rows do not turn red.

Combined Logical Test

To safely check all three rules inside Conditional Formatting:

AND(ISNUMBER($E2); $F2=TRUE())

Part	Meaning
ISNUMBER($E2)	TRUE only if there is a valid date in column E
$F2=TRUE()	TRUE only if the IS IT DUE column is TRUE
AND()	Both conditions must be TRUE to trigger highlighting
$ on the column	Locks the column; the row number changes as the rule applies down the sheet

If either condition is false — no date in E, or F is FALSE — the row stays unformatted.

---

Step-by-Step Setup

Step 0 – Prepare the Sheet

1. Make sure column E contains real dates (the formula bar shows something like 11/14/2025, not 251114).
2. Make sure column F returns actual TRUE/FALSE values using a formula like =TODAY()>=E2.

Step 1 – Select the Rows to Format

Click and drag to select the full range you want to affect, for example:

• A2:F200
• or A2:XFD25839 if you want the rule to span the full width of each row.

Step 2 – Open Conditional Formatting Manager

Go to: Format → Conditional → Manage…

In the dialog, confirm the Cell Range at the bottom matches your selection (e.g. A2:F200).

Step 3 – Add a New Condition

1. Click Add to insert a new condition line.
2. Change the first dropdown from Cell value is to Formula is.
3. In the formula box, enter:

AND(ISNUMBER($E2); $F2=TRUE())

Step 4 – Define the Red Row Style

1. In Apply Style, click the dropdown and choose New Style…
2. Give it a clear name such as DueRowRed.
3. Go to the Background tab and choose a red or light red fill color.
4. Optionally set text to Bold or change the font color for extra emphasis.
5. Click OK to save the style.

Step 5 – Apply and Test

1. Click OK to close the Conditional Formatting dialog.
2. Test a few rows:
   • Put a valid date in E2 that is today or in the past.
   • Confirm F2 evaluates to TRUE (=TODAY()>=E2).
   • The row should now turn red.

Test edge cases:

Scenario	Expected Result
Blank E cell with F=TRUE	Row does not turn red (ISNUMBER returns FALSE)
Future date in E	F formula returns FALSE → row stays neutral
Non-date text in E	ISNUMBER($E2) returns FALSE → row stays neutral

---

Summary

Element	Detail
Business rule	A task is due when it has a target date (E) that is today or earlier, and IS IT DUE (F) is TRUE
Formula column F	=TODAY()>=E2
Conditional format formula	AND(ISNUMBER($E2); $F2=TRUE())
Result	Only real, due tasks highlight red; incomplete or future tasks stay neutral