# LibreOffice / Nextcloud Office – "Due Tasks" Conditional Formatting

1. Goal and Logic

What I want to achieve Highlight whole rows in red when a task is due and must be acted on.

1.1 Core conditions

For a row to be considered due:

1. Target Date condition

   • The Target Date in column E must be today or earlier.
   • In date math: past dates have smaller numeric values than today, and future dates have larger values.
   • So the logical check is: TODAY() >= TargetDate

2. Is It Due flag

   • Column F (e.g., 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.

3. Target Date must exist

   • I don’t want empty rows or rows without a target date to turn red.
   • So column E must contain a real date value, not text and not blank.

1.2 Combined logical test

To safely check all three rules inside Conditional Formatting, I use:

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

• ISNUMBER($E2) → TRUE only if there is a valid date in column E for that row.
• $F2=TRUE() → TRUE only if the IS IT DUE column is TRUE.
• AND() → both conditions must be TRUE for the row to be highlighted.
• The $ locks the column, while the row number (2,3,4,…) is allowed to change as the rule is applied down the sheet.

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

2. Step‑by‑Step: Create the Red "Due" Row Formatting

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 (from a formula like =TODAY()>=E2).

Step 1 – Select the rows to be formatted

1. Click and drag to select the full range of rows you want to affect, for example:

   • A2:F200 (or A2:XFD25839 if you want the rule across the whole row).

This tells Calc which rows will change color when the condition is met.

Step 2 – Open Conditional Formatting Manager

1. Go to the menu/ribbon: Format → Conditional → Manage… (or in the Ribbon: Conditional → Manage…)

2. In the Conditional Formatting dialog, make sure the Cell Range at the bottom matches the rows you selected (e.g., A2:F200 or A2:XFD25839).

Step 3 – Add a new condition

1. Click Add (if needed) to insert a new condition line.
2. In Condition 1, change the dropdown from something like Cell value is to Formula is.
3. In the formula box, enter:

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

This encodes the logic:

• Column E has a valid date and
• Column F is TRUE → then the condition is satisfied.

Step 4 – Define the red row style

1. In Apply Style, click the dropdown and choose New Style… (or an existing style if you already made one).

2. In the style dialog:

   • Give it a clear name like red-bg-cell-251115 or DueRowRed.
   • Go to the Background tab and choose a red or light red fill color.
   • Optionally, set text to Bold or change the font color for emphasis.
3. Click OK to save the style.

Back in the Conditional Formatting dialog, your new style should now be selected in Apply 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.
   • Make sure F2 evaluates to TRUE (=TODAY()>=E2).
   • The entire row (A2:F2 or A2:… depending on your range) should now turn red with the style you defined.

3. Test edge cases:

   • Blank E cell with F=TRUE → row should not turn red.
   • Future date in E with F=TRUE → formula in F should turn FALSE (if =TODAY()>=E2), and the row should not be red.
   • Non-date text in E → ISNUMBER($E2) returns FALSE, so the row remains unformatted.

Once confirmed, you now have a sheet where:

• Only rows with a valid Target Date AND marked as due will highlight in red, clearly signaling they need action.

3. Summary Logic

• Business rule: A task is due for action when it has a target date (E) that is today or earlier, and the IS IT DUE flag (F) is TRUE.
• Formula column (F) checks date vs today: =TODAY()>=E2
• Conditional format on the row checks: AND(ISNUMBER($E2); $F2=TRUE())
• Result: Only real, due tasks light up red for attention; incomplete or future tasks stay neutral.