OmniDocExcel conditional format changes color by date: automatic reminder when expiration is approaching
Use the formula rules of conditional formatting to make the expiration date in Excel turn red when it is overdue and turn orange when it is close to expiration. You can see the matters that need to be processed when you open the table. The article gives directly applicable formulas, setting steps and common failure causes.
Key points of this article
- Use the "Use formula to determine the cells to be formatted" rule, which is more flexible than the simple "greater than/less than" rule and can exclude completed items at the same time.
- The core of the overdue judgment formula is to compare the date with TODAY(). Every time the table is opened or recalculated, the color will be automatically updated with the current day's date.
- It is recommended to set the window for "from today to the next 7 days" when expiration is approaching, and set the color separately from the overdue rules for easy distinction at a glance.
- The color failure is mostly due to the fact that the date is actually text, the formula reference does not lock the column, or the priority of the two rules is set improperly.
- Conditional formatting is only responsible for visual reminders and will not actively pop up windows or send messages. You need to open the form regularly to view it.
If you are responsible for a contract ledger, project task list, or license list, you are afraid that a certain due date will pass by quietly before you are discovered. Excel's conditional formatting allows the expiration date to "alert" itself: those that are overdue will turn red, and those that are due in a few days will turn orange, so there is no need to manually search for them every time.
This article is written for people who frequently maintain date tables. After reading, you will know how to use formula rules to change colors by date, how to prevent completed items from being falsely reported, and why colors sometimes do not appear. The following example is based on a task table, the date is in column C, the status is in column D, and the data starts from row 2.
Which rule is used to change color by date? One sentence conclusion
The safe way to change color by date is to use "use a formula to determine the cells to be formatted", write the judgment conditions as formulas, and then create a rule for overdue and approaching expiration.
| your situation | Recommended practices | reason |
|---|---|---|
| Just want the expiration date to change color | One formula rule: Date is less than TODAY() | Easy to set up, no additional judgment required |
| Both overdue and imminent expiration should be considered | Two formula rules, set different colors respectively | Color-coded, easy to control priorities |
| There are status columns such as "Completed" in the table | Add status judgment to the formula | Completed items will not always be displayed as overdue |
| Dates are spread across multiple columns or tables | Set separate rules for each column, or use the same formula to apply to the entire range | Avoid missing reminders for a certain column |
Scenario: A ledger of due matters
Let's say you're responsible for a 12-page contract ledger, or a project task sheet with about 50 lines. Column C is "Due Date" and column D is "Status", with values of "In Progress" or "Completed". You want to see immediately when you open the table which items are overdue and which ones are due within a week.
If you only rely on manual reading of dates line by line, it will be easy to miss them when there are too many lines. Moreover, many people will set the date in text format, but the conditional formatting will not take effect at all, so before doing so, try to confirm that the date column is indeed a date value.
Complete process
- Select the column with the expiration date, for example C2:C200. The selection starts on row 2 and does not include the header.
- Switch to the Home tab, find Conditional Formatting, create a new rule, and select Use formula to determine the cells to be formatted.
- Enter the overdue judgment formula in the formula input box:
=AND(LEN($C2)>0,NOT($D2="已完成"),TODAY()>$C2). Column C in the formula must be written as$C2, with the columns locked and rows relative, so that it will be applied correctly to the entire selection. - When setting the format, set the fill color to red or light red, and be sure to save this rule.
- Create a new formula rule again, enter the approaching expiration formula:
=AND(LEN($C2)>0,NOT($D2="已完成"),$C2>=TODAY(),TODAY()+7>=$C2), and set the fill color to orange. The 7 here means that within the next 7 days, it can be changed to other days according to your reminder cycle. - In the rule management interface, check whether the applicable scope of the two rules is C2:C200, and adjust the order so that the overdue rules are ranked first.
- Enter a few test dates, such as yesterday, today, three days from now, and one month from now, to confirm that the colors are as expected. Delete the test data after the test is completed.
Where things can easily go wrong
The date looks normal, but the color does not appear. The common reason is that the date is actually text. If there is a small green triangle in the upper left corner of the cell, or the date is aligned to the left, it is probably text. You can select the column, use the data column or text to date function to convert it into a real date, and then test the rule.
Completed items are still shown in red. This is because the status column is not excluded from the formula. Check whether the formula contains NOT($D2="已完成"), and confirm that the text in the status column is exactly the same as what is written in the formula, including Chinese punctuation and spaces.
Two colors cover each other. When a date satisfies both overdue and imminent expiration, only the format of the rule with the highest priority will be displayed. The overdue condition itself will not satisfy the approaching expiration, so the two usually do not conflict. However, if the formula conditions you write overlap, you need to adjust the order in the rule management interface.
The colors are not updated after opening the form. TODAY() will be updated when the table is recalculated, so the color will automatically change with the date. If you don't close the file for a long time, you can press F9 once, or reopen the file to refresh the results.
Conditional formatting will not automatically remind you. It can only change the color of cells, it will not pop up notifications, and it will not send emails. If the matter is very important, it is recommended to open the ledger once a day or every week, or use a formula to count the overdue amount at the top of the table for quick viewing.
The area range is written too short. If the newly added row exceeds C2:C200, it will not be changed color. You can expand the range to a range that significantly exceeds the actual data, or you can convert the data range into a table and let the rules automatically expand as new rows are added.
🎯 Which method is suitable for you?
If you just need to know which items are overdue
Just create one overdue rule and compare the date with TODAY() in the formula. It is quick to set up and less error-prone.
If you want to be warned a few days in advance
Add a second near-expiry rule, set the window to 3 days, 7 days, or 30 days, and use a different color than Overdue.
If there are statuses such as completed or canceled in the table
Be sure to exclude these statuses from the formula, otherwise the completed items will always be displayed as overdue, and the reminder will lose its meaning.
If the date comes from a file exported by another system
First check whether the date is a real date value, and then set the rule. Dates copied from web pages or other software are often in text format and need to be converted first.
❓ Frequently Asked Questions (FAQ)
1. Can conditional formatting automatically change color based on date?
Can. As long as the date is a real date value and you use a formula to compare it with TODAY(), the color will automatically change according to today's date without manual modification.
2. Will TODAY() be updated all the time?
Updates when the table is opened or recalculated. If the file has not been closed for a long time, press F9 to refresh or reopen the file.
3. Can expiration reminders be sent to mobile phones or email addresses?
Conditional formatting itself can't do that. It only changes the color of the cell. To notify others, you need to use other reminder methods, such as calendar software or regularly sent reports.
4. What should I do if my date is copied from a webpage and the color does not take effect?
Such dates are usually text. You can convert data into columns or convert text into dates, and then check whether the rules are effective.
5. What should I do if there are many overdue items and the colors are too messy?
You can keep only the eye-catching color of overdue, and use lighter colors when expiration is approaching, or use a formula at the top of the table to count the number of overdue to reduce the number of judgments based on color.
📝 Summary
The key to using formula rules to change colors by date lies in three points: confirm that the date is a real date value, exclude completed items from the formula, and separate overdue and near-due rules into two rules. After the settings are completed, you can open the table to see the due matters that need to be prioritized.
