About this article
This article was generated using an automated generation workflow utilizing generative AI. Based on the official Microsoft support documentation for conditional formatting, the TODAY function, and the AND function, we organized a method to color-code expired and upcoming deadlines on a row-by-row basis using a sample deadline management table.
Verification Status: 📘 Confirmed with official Microsoft specifications / Not verified on actual Excel hardware
Information verification date: October 11, 2026. The following color-coding results are expected outcomes derived from formula logic and have not been validated on an actual device.
- Supported Environments, Capabilities, and Limitations (Confirmed October 15, 2026)
- When colors overlap for expired and near-deadline items
- How to verify in the web and desktop versions
- Table to Create First
- Red: Overdue incomplete rows
- Yellow: Incomplete rows due within 7 days from today
- Expected Color-Coding Results
- Troubleshooting when it does not work as expected
- Handling blank values, completion status, and timestamps separately
- Try changing one part: Change "within 7 days" to "within 3 days"
- For business use,
- Official Microsoft Documentation
Supported Environments, Capabilities, and Limitations (Confirmed October 15, 2026)
| Item | Scope and limitations |
|---|---|
| Target | Excel conditional formattingSeparate from document generation features using Copilot |
| Compatibility | Microsoft 365 Excel, Excel 2024, and other supported desktop and web versions (user interfaces may vary) |
| User Account | Personal and enterprise Excel users. Copilot license is not required |
| Operation Data | Selected cell range in the currently open sheet |
| What you can do | TODAY()andAND()Color-code rows by condition based on expired, near-deadline, or completed status using this. |
| Limitations | This formatting alone does not trigger email sending or reminder registration. |
| Row count limit | Constraints on overall Excel sheet limits and processing performance. This is separate from Copilot's file count limits. |
| Caution | If dates remain as text strings, comparisons may not yield the expected results. Also, pay attention to time zones and recalculations. |
Official source:Using formulas in conditional formatting、Combination of IF and AND。
When colors overlap for expired and near-deadline items
In a table where column C has the deadline and column D has the status, you can separate expired items as=AND($C2<>"",$D2<>"完了",$C2<TODAY())and items within 7 days from today as=AND($C2<>"",$D2<>"完了",$C2>=TODAY(),$C2<=TODAY()+7)Assuming the target range is$A$2:$D$100,by adding $ before the column reference while keeping the row number relative, you can evaluate the same deadline column and status column for each row.
For example, if C4 contains an Excel date 5 days from today and D4 is "Incomplete", it qualifies for yellow; if C6 is yesterday and D6 is "Complete", it qualifies for no color. However, if dates are entered as text strings like "2026/10/15" or if the status contains trailing spaces like "Complete ", comparisons and conditions may change unexpectedly.
How to verify in the web and desktop versions
Although the formula logic is the same, the screen for adding conditional formatting and the rule management panel differ depending on the version and platform. Select the web or Windows version instructions on the official Microsoft page and verify whether the option to "Use a formula to determine which cells to format" appears on your screen. Do not conclude that the feature is unsupported simply because you cannot find the button location.
When used in operational tables, relying solely on yellow or red highlights can be supplemented by displaying a status column or the number of expired items separately to reduce oversights. Color coding does not involve moving or deleting files; it is an operation that changes the display while preserving the underlying data values. The permissions, execution conditions, and limits differ from asking Copilot to "manage deadlines" or creating a notification in Power Automate. The publication results are expected examples and do not constitute actual Excel testing.
When managing submission or contract renewal deadlines in Excel, lists consisting solely of dates are easy to overlook.Highlight expired items in red, items within 7 days from today in yellow, and leave completed items uncoloredThis allows you to quickly find rows that require review. Here, this is achieved without macros using conditional formatting andTODAY().
The key points are three: "do not color if the date is blank," "do not color if completed," and "do not overlap the expired and approaching deadline conditions." Simply setting a condition like "deadline is before today" may unintentionally highlight blank cells or completed rows.
Table to Create First
Enter the following example into cells A1:D6 of a new worksheet. Please enter the dates as Excel dates, not as text. For verification purposes, you may replace them with your own current date while reading the instructions.
| Row | A: Task | B: Assignee | C: Deadline | D: Status |
|---|---|---|---|---|
| 2 | Invoice Review | Tanaka | Yesterday's Date | Incomplete |
| 3 | Monthly Report | Sato | Today's date | Incomplete |
| 4 | Review quotation | Suzuki | Date 5 days from now | Incomplete |
| 5 | Contract renewal | Takahashi | Date 20 days from now | Incomplete |
| 6 | Organize vouchers | Yamada | Yesterday's date | Completed |
As an example of date input, for C2=TODAY()-1, and for C3=TODAY(), for C4 you can enter=TODAY()+5, for C5 you can enter=TODAY()+20, for C6 you can enter=TODAY()-1. If the display turns into numbers, please change column C to the date format.
With this input method, the relative relationships such as "yesterday, today, 5 days later, 20 days later" are maintained even if the reading date changes. However, in actual operations, please enter confirmed dates in the deadline column. If you use relative date formulas as actual deadlines, the deadlines themselves will shift every day.
Red: Overdue incomplete rows
First, select A2:D100.Make sure that the upper-left cell of the selection is A2. Go to [Home] -> [Conditional Formatting] -> [New Rule] -> [Use a formula to determine which cells to format], and enter the following.
=AND(ISNUMBER($C2),$C2<TODAY(),$D2<>"完了")
Specify a light red fill. This formula evaluates to TRUE when column C contains a valid Excel date, it is earlier than today, and column D is not "Completed".
ISNUMBER($C2): Verifies if the deadline is a numeric value such as an Excel date serial value. Blank cells and strings that look like dates are excluded.$C2<TODAY(): Returns TRUE if the deadline is earlier than today.Rows due today are not overdue。$D2<>"完了": Excludes completed items.AND(...): Colors only rows that satisfy all three conditions.
$C2The$is a marker to fix column C. Because no$is attached to the row number, each row in A2:D100 can reference C2, C3, C4, etc.$C$2, all rows would reference only C2, preventing row-by-row evaluation.
Yellow: Incomplete rows due within 7 days from today
Create another new rule for the same range A2:D100 and set a light yellow fill.
=AND(ISNUMBER($C2),$C2>=TODAY(),$C2<=TODAY()+7,$D2<>"完了")
The target range covers up to 7 days from today.>=TODAY()Since this condition exists, it does not overlap with the red overdue condition. If you do not want to include the 7th day,<=change<to . Defining how the boundary of 'within 7 days' is determined across your team prevents confusion.
TODAY()returns a value representing today's date. Since Excel manages dates as sequential serial numbers,TODAY()+7represents the date 7 days from now. If column C contains timestamp values, the comparison result may vary depending on the time of day, so you should either format the deadline column to dates only or design a separate evaluation formula.
Expected Color-Coding Results
When using the example above, the expected results are as follows.
| Row | Deadline | Status | Expected Color | Reason |
|---|---|---|---|---|
| 2 | Yesterday | Incomplete | Red | Deadline is prior to today |
| 3 | Today | Incomplete | Yellow | Within 7 days from today |
| 4 | In 5 days | Incomplete | Yellow | Within 7 days |
| 5 | In 20 days | Incomplete | None | Plenty of time until the deadline |
| 6 | Yesterday | Completed | None | Completed |
This is the expected value derived from the formula. It is not an actual measurement of colors or rule behavior in the actual Excel screen. If the colors differ from expected, perform the following troubleshooting steps.
Troubleshooting when it does not work as expected
Dates appear visible but are not highlighted: The cell may have been imported as text. Enter a formula in another cell=ISNUMBER(C2)and check if it evaluates to TRUE. Strings imported from CSVs, such as "2026/10/11", may look like dates but are not necessarily valid calculation dates. Keep the original value, verify the source format, and then convert it to a date type.
All rows are highlighted in the same color：$C$2Check whether absolute references are incorrectly used for the row like in, and verify that the applies-to range is set to A2:D100. Formulas must be written relative to the top-left cell of the selection. If you selected starting from A3,$C3use$D3as the reference.
Completed rows turn red: Check if column D contains trailing spaces like "Completed ". Using data validation lists to select "Incomplete" or "Completed" can reduce naming inconsistencies. If statuses are mixed (e.g., "Done", "Completed"), it is best to standardize the status column first for easier management.
Red and yellow rules conflict: The two formulas here are designed to be mutually exclusive based on date comparisons. If other conditional formats remain, check the apply-to range, priority, and stop-if-true settings via Conditional Formatting -> Manage Rules. Microsoft's official documentation also advises paying attention to the rule evaluation order when using multiple rules.
Colors do not update when the date changes：TODAY()depends on recalculation. Check if the calculation mode is set to manual and recalculate if necessary. This is not an automated notification system that runs while the file is closed.
Handling blank values, completion status, and timestamps separately
In deadline management trackers, ambiguous color meanings will cause operational breakdown. The following three points should be decided in advance.
First,rows with blank deadlines are not highlighted under this rule. A blank cell may mean "Unset" rather than "No deadline". If you also want to warn about unset deadlines, indicate it with a distinct color (other than red or yellow) or use a "Deadline Missing" column. Treating overdue items and missing entries with the same red color makes it difficult to determine what needs to be fixed.
Next,Completion determination depends on the character string in column D. In practice, standardize the state using data validation or a check column, and track who made the update as needed. Do not treat the lack of color as "proof of processing"; ensure that the source data can be verified.
Finally,dates and date-times are different entities. For example, when setting a deadline for 6:00 PM today, you cannot determine whether 6:00 PM has passed using date-only rules alone. If you need to handle time-based deadlines, an alternative design usingNOW()or similar is required. This guide is limited to "day-based deadline management."
Try changing one part: Change "within 7 days" to "within 3 days"
Let us change onlyTODAY()+7in the yellow formula toTODAY()+3.
=AND(ISNUMBER($C2),$C2>=TODAY(),$C2<=TODAY()+3,$D2<>"完了")
The expected result is that row 3, which is due today, remains yellow, while row 4, which is due in 5 days, loses its color. Because the red overdue condition has not been changed, row 2 remains red. By observingwhich rows change when a single threshold is modified, you can understand conditional formatting without memorizing formulas.
For business use,
first verify the color-coding on a small test table and create a backup before applying it to important existing ledgers. If you set the application range to a fixed range such as A2:D100, rows 101 and below may not automatically be included. For workflows where rows increase, consider using Excel tables or reviewing the applied range.
Deadline color-coding isa mechanism to improve discoverability, and it does not guarantee notifications to personnel, approvals, update histories, or task completion. For critical deadlines, combine it with regular verification procedures and clear ownership.
Additionally, rather than relying solely on red and yellow, it is important to retain status columns and due dates as text. This ensures that information is not lost even when printed in black and white or when color distinction is difficult. Conditional formatting is best used as an auxiliary feature to highlight decision-making data.
Official Microsoft Documentation
Highlight information in Excel using conditional formatting：Formula rules, applies-to ranges, precedence, and deadline examples.
Using IF with AND, OR, and NOT functions in Excel：Specifying conditional formatting with AND.
TODAY function：Handling date serial numbers and recalculation.
Information verification date: October 11, 2026. This targets daily deadline management on Excel worksheets. Since physical device verification has not been performed, display results are documented as expected values.
