View source for 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 <code>E</code> must be '''today or earlier'''. Past dates have smaller numeric values than today, so the check is: <code>TODAY() >= TargetDate</code>

; Is It Due flag
: Column <code>F</code> ('''IS IT DUE''') must evaluate to '''TRUE'''. Typically this is a formula in F2 like <code>=TODAY() >= E2</code>, which returns TRUE when the target date is today or in the past.

; Target Date must exist
: Column <code>E</code> 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:

<syntaxhighlight lang="text">
AND(ISNUMBER($E2); $F2=TRUE())
</syntaxhighlight>

{| class="wikitable"
|-
! Part !! Meaning
|-
| <code>ISNUMBER($E2)</code> || TRUE only if there is a valid date in column E
|-
| <code>$F2=TRUE()</code> || TRUE only if the IS IT DUE column is TRUE
|-
| <code>AND()</code> || Both conditions must be TRUE to trigger highlighting
|-
| <code>$</code> 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 ===

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

=== Step 1 – Select the Rows to Format ===

Click and drag to select the full range you want to affect, for example:
* <code>A2:F200</code>
* or <code>A2:XFD25839</code> 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. <code>A2:F200</code>).

=== Step 3 – Add a New Condition ===

# Click '''Add''' to insert a new condition line.
# Change the first dropdown from <code>Cell value is</code> to '''<code>Formula is</code>'''.
# In the formula box, enter:

<syntaxhighlight lang="text">
AND(ISNUMBER($E2); $F2=TRUE())
</syntaxhighlight>

=== Step 4 – Define the Red Row Style ===

# In '''Apply Style''', click the dropdown and choose '''New Style…'''
# Give it a clear name such as <code>DueRowRed</code>.
# 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 extra emphasis.
# Click '''OK''' to save the style.

=== Step 5 – Apply and Test ===

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

Test edge cases:

{| class="wikitable"
|-
! Scenario !! Expected Result
|-
| Blank E cell with F=TRUE || Row does '''not''' turn red (<code>ISNUMBER</code> returns FALSE)
|-
| Future date in E || F formula returns FALSE → row stays neutral
|-
| Non-date text in E || <code>ISNUMBER($E2)</code> returns FALSE → row stays neutral
|}

----

== Summary ==

{| class="wikitable"
|-
! 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 || <code>=TODAY()>=E2</code>
|-
| Conditional format formula || <code>AND(ISNUMBER($E2); $F2=TRUE())</code>
|-
| Result || Only real, due tasks highlight red; incomplete or future tasks stay neutral
|}