Collabora Online Calc · Conditional formatting · Intermediate
Highlight Overdue Dates in Collabora Online Calc
Use a formula rule with blank, text-date and completed-task guards; test against a fixed reference date before switching to TODAY.
Official documentation reviewed; independent synthetic checks; not tested in Collabora applications. · Editorial standards
Use a Formula is conditional-format rule that checks three things: the due date is numeric, it is before the reference date, and the task is not Done. Test with a fixed reference date first so the expected colors are reproducible; then use TODAY for a live tracker.
Scope and sample
Collabora documents Online conditional-format dialogs in its interface announcement. Collabora Office 26.04 help defines Formula is and styles. The Online toolbar and rule editor can differ by build; use its Conditional Formatting controls, not an assumed desktop menu path. If Formula is is unavailable, stop rather than substituting a date-only rule that would also highlight completed tasks.
On a practice sheet, use A1:C7 with Task, Due and Status headers. Enter the dates with DATE formulas to avoid an ambiguous CSV date import. A plain-text setup file provides the same entries.
| Row | Task | Due entry in B | Status |
|---|---|---|---|
| 2 | Draft | =DATE(2026;4;2) |
Open |
| 3 | Review | =DATE(2026;4;3) |
Open |
| 4 | Send | =DATE(2026;4;1) |
Done |
| 5 | Schedule | leave empty | Open |
| 6 | Archive | =DATE(2026;4;4) |
Open |
| 7 | Text-date check | '2026-04-02 |
Open |
The leading apostrophe in B7 deliberately creates text; it is not part of the displayed date. Format the numeric date cells as dates. Put Reference date in E1 and =DATE(2026;4;3) in E2. Semicolons are used here; adapt separators to your installation.
Apply and test the rule
- Select A2:C7 with A2 as the starting cell. Open the Online Conditional Formatting editor (the documented classic interface exposes it in the Format menu) and add a Formula is rule for that exact range.
- Enter
AND(ISNUMBER($B2);$B2<$E$2;$C2<>"Done")in the formula field. Use the formula expression rather than a textual label. The criterion must evaluate to TRUE/nonzero. - Choose a clearly visible style, such as a contrasting fill with readable text, and confirm. Keep an explicit Status column so color is not the only way to understand the tracker.
- With E2 fixed to 3 April 2026, only row 2 should be highlighted. Row 3 is due today, row 4 is completed, row 5 has no date, row 6 is future, and row 7 is text rather than a real numeric date.
- Change E2 to
=DATE(2026;4;4). Rows 2 and 3 should now highlight; row 6 is still due on the reference day and is not overdue. Restore the reference date afterward.
The dollar signs lock the Due and Status columns but allow the row number to change for each record. $E$2 locks the reference date completely. Starting the rule at row 1 while using $B2 would shift the logic relative to the intended data.
Switch to the current date
Replace E2 with =TODAY(). Keep the rule unchanged. Collabora’s TODAY documentation says it returns the system date and recalculates when recalculation occurs, including document reopening or value changes. This is not a guaranteed midnight alarm or a background reminder.
Check the date E2 actually displays in your hosted Online environment; do not assume its date boundary matches the local device. Ensure automatic calculation is enabled where the deployment exposes it. If dates appear stale, reopen/recalculate through supported controls and verify E2 before trusting the highlight. The fixed April example will have different results when TODAY advances.
Troubleshooting
Use ISNUMBER to find text dates and AND to understand the combined checks. Convert imported date strings deliberately, confirming their original date order. Numeric garbage is still numeric, so this rule does not validate the plausibility of dates.
If completed rows highlight, inspect the exact Status text and trailing spaces. If every row colors the same way, check absolute versus relative row references and other overlapping rules. Extend the conditional-format range when adding records; do not assume it covers every future row. A due timestamp later today remains not overdue under this strictly-before-today policy.
Testing disclosure: The fixed-date truth table was checked independently. No Collabora conditional formatting or automatic recalculation was executed; rule UI, style application and date-boundary behavior require a check in your deployment.