OmniDocConditional Formatting for Dates and Expiry Dates
Learn how to highlight past-due, expired and soon-to-expire dates in a spreadsheet using custom date rules, with steps and checks you can reuse.
If you track contract renewals, license expiries or invoice due dates in a spreadsheet, you need overdue rows to stand out without reading every line. Conditional formatting does this by changing a cell's color when its value meets a rule. For dates, the rule compares each cell with today's date, so the highlighting updates on its own as time passes.
This guide shows how to set up date-based conditional formatting for three common cases: dates already in the past, dates that have expired, and dates falling within a window you choose. It focuses on the highlighting rules themselves. If you need to calculate a date, such as adding 90 days to a start date, that is a separate topic covered by date formulas.
The rules are created in your spreadsheet program, not in OmniDoc. OmniDoc's role comes in only if your dates sit in a PDF table, which you can export to a spreadsheet format first. The steps below work in any spreadsheet program that supports custom-formula conditional formatting and saves to .xlsx.
How do you highlight past-due or expiry dates? The short answer
Create a rule with a custom formula that compares each date with TODAY(), then give matching cells a fill color. Use one rule for expired dates and a second rule for dates expiring soon.
| Your situation | What to do | Why |
|---|---|---|
| You want past or expired dates flagged | Add a rule with the formula =AND(LEN(A2)>0,TODAY()>A2) and a red fill | The formula updates each day, and the length check skips empty rows |
| You want dates expiring soon flagged | Add a second rule with =AND(A2>=TODAY(),TODAY()+30>=A2) and an amber fill | It shows dates approaching their deadline, and the 30-day window is your choice |
| You want the whole row highlighted | Apply the formula to the full table, using a column reference such as =TODAY()>$A2 | One date column can control the color of every field in the row |
| Some dates look right but never match | Convert text dates to real dates before adding rules | Date rules compare numbers, so text values are skipped |
| Your dates are in a PDF table | Export the table to a spreadsheet format, then add the rules | PDFs cannot hold conditional formatting rules |
Before you start
Check these points before you build the rules. Each one prevents a common reason the highlighting does not appear.
- Confirm the dates are real dates. Real dates usually align to the right of the cell, while text dates align to the left. A text date is skipped by date comparisons, so the rule will never fire for it.
- Choose the range and its first row. Relative references such as A2 refer to the top-left cell of the range you select. If you start the selection in the wrong row, the rule will check the wrong cells.
- Decide what "today" means for your file. TODAY() returns the date each time the file recalculates, which is when it opens or changes. If you need a fixed cutoff, such as the end of a quarter, put that date in its own cell and reference that cell instead.
- Set the window for approaching dates. The example uses 30 days. Pick the number that matches how far ahead you need warning, and write it down so colleagues know what the amber color means.
- Scan PDFs before you export them. If the table came from a scanned PDF, run OCR in the OmniDoc desktop client first, so the dates become selectable text. You can download the client at download.omnidoc.top.
Setting an expiry date rule, step by step
- Select the date range. Click the first data cell under the header, then extend the selection to the last row you need, such as A2:A200. Starting at the first data row keeps the header out of the rule.
- Open the conditional formatting options. Look for the option that lets you enter a custom formula. Choose it so you can write the rule yourself rather than picking a preset.
- Enter the expired-date formula. Type =AND(LEN(A2)>0,TODAY()>A2). The first part skips blank cells, and the second part flags any date earlier than today. Because A2 is the top-left cell of your selection, the rule adjusts for each row below it.
- Set the format. Choose a light red fill and dark red text. Using a color that does not depend on the default cell style makes the warning easy to spot at a glance.
- Add the approaching-date rule. Select the same range again, add a new custom formula rule, and enter =AND(A2>=TODAY(),TODAY()+30>=A2). Give it an amber fill so it is clearly different from the expired color.
- Check that the two rules do not overlap. An expired date is before today, and an approaching date is today or later, so one cell cannot match both rules. You can confirm this by testing a past date and a future date in the same column.
- Save the file as .xlsx. Saving in a spreadsheet format keeps the rules with the data. If you send the file to someone who uses another program, confirm that they still see the colors when they open it.
Check the result
Once the rules are in place, test them with sample values so you know they behave as expected. Use a copy of the file if the data is important.
| Check | How to see it | If it's wrong |
|---|---|---|
| Past dates are colored | Type yesterday's date in an empty row inside the range | Confirm the formula's first cell matches the top-left cell of the selected range |
| Blank rows stay uncolored | Look at rows below your last date | Make sure the length check (LEN(A2)>0) is in the formula |
| Dates in the window are amber | Type a date 10 days ahead | Check the TODAY()+30 part and the rule's order against the expired rule |
| Text dates are not skipped | Look for left-aligned values in the column | Re-enter them as real dates, or convert them with a date function such as DATEVALUE |
| Colors change on a new day | Reopen the file on a later day | If colors stay the same, check that the program recalculates automatically |
📊 At a glance
Two approaches cover common date-highlighting needs, and the table below compares them.
| Approach | Good fit for | Trade-off |
|---|---|---|
| Single-cell rule on the date column | Quick flags on one list of dates | Only the date cell changes color |
| Whole-row rule using a column reference | Tables where each row needs to stand out | Requires a correct $ reference so the row stays tied to its date |
❓ FAQ
1. Can I flag dates that expired more than 30 days ago?
Yes. Use a custom formula such as =AND(LEN(A2)>0,TODAY()-30>A2). This only colors dates that are at least 31 days in the past, so you can separate long-overdue items from recent ones.
2. Does OmniDoc add conditional formatting to my spreadsheet?
No. OmniDoc's facts do not include conditional formatting, so you need to create the rules in your spreadsheet program. OmniDoc can help earlier in the process by exporting a PDF table to a spreadsheet format or running OCR on a scanned table.
3. What should I look for in a free spreadsheet program?
Check that it supports custom-formula conditional formatting, because that is what date rules need. Also confirm that it saves rules in .xlsx files, and test that a colleague using another program still sees the colors.
📝 Summary
Conditional formatting turns a plain date column into a live warning list. Use one custom formula for expired dates, add a second rule for approaching ones, and test both with sample values before you rely on them.
