To color dates in Excel, select the date cells, then choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring and pick a time period and format. For more specific rules—such as highlighting dates due in the next seven days—use Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
What date conditional formatting does
Conditional formatting changes how a cell looks when its value meets a condition. For dates, you can use it to draw attention to deadlines, recent entries, upcoming appointments, or dates that fall within a chosen period. The cell’s value does not change: a fill color, font color, or other formatting is applied while the condition is true.
Excel stores genuine dates as serial numbers, with the displayed date controlled by the cell’s number format. That means a date rule evaluates the underlying value, not the way it looks on screen. Two cells that display as 3-Nov-2026 and November 3, 2026 can be treated as the same date if both contain valid Excel dates.
Conditional formatting is useful for visual scanning, but it is not a substitute for checking the underlying data. If a date was imported as text, Excel may not recognize it as a date at all. Correctly formatted date values and a well-chosen rule are both essential for reliable results.
Use a built-in date rule
For common time periods, the built-in A Date Occurring rule is usually the quickest option. In Excel for Microsoft 365 on Windows:
- Select the cells that contain dates, such as A2:A100. You can select a larger range if you expect to add more rows.
- Choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.
- In the dialog, choose a period, such as yesterday, today, tomorrow, last 7 days, last week, this week, next week, last month, this month, or next month.
- Choose a preset format or select Custom Format to set a fill, font, border, or number format, then confirm the rule.
The exact choices shown in the date-period list may depend on the Excel interface and version. These rules are convenient for calendar-style periods, but they do not let you specify every possible interval. For example, a requirement such as “the next 14 days, including today” is easier to express with a formula rule.
On Excel for Mac, the Conditional Formatting commands are available from the Home tab as well, although dialog layout can differ. Excel for the web also has conditional formatting options, but some editing controls and workflows may vary from the desktop app. If you cannot find the same dialog, look for Conditional Formatting on the Home tab and check the rule options available in that version.
Build custom rules with formulas
A formula-based rule lets you define the condition precisely. Select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for cells that should receive the format. Choose Format to select the color, and confirm the rule.
For each example below, the date values are in A2:A100, and the conditional formatting rule applies to that same range. The formula reference starts at A2 because that is the top-left cell in the selected range. Excel adjusts the row reference as it evaluates each cell.
- Highlight dates before today: With dates in A2:A100 and the rule applying to A2:A100, use
=AND(ISNUMBER(A2),A2<TODAY()). The ISNUMBER check prevents blank cells and text from being treated as overdue dates. - Highlight today: With dates in A2:A100 and the rule applying to A2:A100, use
=AND(ISNUMBER(A2),A2>=TODAY(),A2<TODAY()+1). This matches any time on today’s date, not just a value with no time component. - Highlight dates from today through the next six days: With dates in A2:A100 and the rule applying to A2:A100, use
=AND(ISNUMBER(A2),A2>=TODAY(),A2<TODAY()+7). The lower boundary includes today; the upper boundary excludes the date seven days from today. - Highlight dates at least 30 days in the past: With dates in A2:A100 and the rule applying to A2:A100, use
=AND(ISNUMBER(A2),A2<=TODAY()-30). This includes the date exactly 30 days before today.
These rules use TODAY(), which returns the current date according to Excel’s calculation environment. Because the result can change when the workbook recalculates on a later day, the highlights update over time. If the workbook uses manual calculation, the visible results may not refresh until it recalculates.
Color an entire row based on its date
You can highlight a whole record instead of only its date cell. Suppose each record is on one row, dates are in column A, and the data spans A2:D100. Select A2:D100 before creating a formula rule. With dates in A2:A100 and the rule applying to A2:D100, use =AND(ISNUMBER($A2),$A2<TODAY()) to color each row whose date is before today.
The dollar sign before A locks the date column, so Excel checks column A for every cell in the selected row. The row number remains relative, allowing the rule to check A2 for the first row, A3 for the second, and so on. If you lock the row number too, Excel may test the same date for every row. If the date is in another column, replace A with the correct column letter.
After creating the rule, review its Applies to range in Conditional Formatting Rules Manager. A correct formula can appear to behave incorrectly if the range starts on a different row than the formula assumes. For instance, if the range begins at row 3, the relative row reference should normally begin at row 3 too.
Choose a date color scheme that stays readable
Color is most useful when it communicates a small number of clear states. A typical schedule might use one color for overdue items, another for today, and a third for upcoming deadlines. Use a restrained palette and keep text contrast high; a bright fill with a readable font is usually easier to scan than several similar shades.
If you create separate rules for overdue, today, and upcoming dates, make their conditions mutually exclusive where practical. Otherwise, more than one rule may be true for the same cell. Excel evaluates rules according to their order in Conditional Formatting Rules Manager, and options such as Stop If True can affect which formatting is displayed. Review rule order when colors do not match the condition you expected.
Color should not be the only signal if the workbook needs to be accessible or printed in grayscale. Consider adding a status column with text such as Overdue or Due today, or use a symbol alongside the formatting. That gives readers another way to understand the information without relying on color alone.
Handle times, blanks, and date ranges
A common surprise is that a cell can contain both a date and a time. A value for 3-Nov-2026 at 2:00 p.m. is later than the serial value for 3-Nov-2026 at midnight. As a result, a direct equality test against TODAY() may fail for dates that include times. The today formula above avoids that problem by checking whether the value is at least today and less than tomorrow.
Blank cells deserve attention too. Depending on the condition, empty cells may appear to satisfy a comparison or may be ignored in ways that are confusing. ISNUMBER is a useful guard when the range should contain genuine Excel date values, because Excel dates are numeric serial values. It also prevents text entries from being treated as valid dates by these examples.
To highlight a fixed date interval rather than a moving period, use date boundaries in a formula or compare against cells that hold the start and end dates. For example, if a report’s start date is in F1 and its end date is in G1, then with dates in A2:A100 and the rule applying to A2:A100, use =AND(ISNUMBER(A2),A2>=$F$1,A2<=$G$1). This includes both boundary dates. Make sure F1 and G1 contain real dates and that the start date is not later than the end date.
Fix common conditional-formatting problems
If the expected cells do not change color, check the data and rule before rebuilding the workbook. A date that looks correct may be text, particularly after copying data from a website or importing a CSV. Changing its number format alone does not convert text into a genuine date. You may need to convert the values using Excel’s data tools or re-enter them as dates, then verify that the rule works.
- Check the Applies to range: Open Home > Conditional Formatting > Manage Rules and confirm the rule covers the intended cells.
- Check the formula’s starting reference: The formula should refer to the top-left cell of the selected range, with dollar signs used only where references need to stay fixed.
- Check for hidden time values: Use a range test such as greater than or equal to today and less than tomorrow when matching a whole calendar day.
- Check rule order and overlap: In Manage Rules, inspect which rules apply and whether their order or Stop If True setting changes the displayed format.
- Check the chosen format: A rule can be working even if its fill color is too subtle, overridden by another rule, or difficult to distinguish from the normal cell style.
For a quick test, temporarily choose an obvious fill color and apply the rule to a small range with known dates, including one date before today and one after it. This helps distinguish a formula problem from a formatting or range problem.
Frequently Asked Questions
Can Excel automatically color dates that are past due?
Yes. Apply a formula rule to the date range and use a condition that checks whether each numeric date is earlier than TODAY(). Add ISNUMBER if blanks or text may occur in the range, then choose the fill color you want.
How do I highlight dates within the next seven days?
Use a formula rule with a lower boundary of TODAY() and an upper boundary of TODAY()+7. That includes today and the next six dates. If you mean the next seven full days after today, use TODAY()+1 as the lower boundary instead.
Why does a rule for today miss some dates?
The cells may contain times as well as dates. A direct equality check can miss values later than midnight. Check for values from today up to, but not including, tomorrow to include every time on the current date.
Can conditional formatting color a whole row from one date cell?
Yes. Select the full data range and write a formula that locks the date column but leaves its row relative, such as the row-highlighting example above. Confirm that the formula’s row matches the first row of the selected range.
Does changing a date’s display format fix a rule that does not work?
Not necessarily. Number formatting changes how a value appears, but it does not convert text into an Excel date. If the rule fails, check that the cells contain real date values and not date-looking text.
Related
- Company Invoice Template in Excel: Setup and Tips
- Compare Lists in Excel: Find Matches and Differences
- Combine Two Cells in Excel and Keep Both Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Loan Payment Formula: How to Use PMTFor a fixed-rate loan with monthly payments, Excel’s PMT function calculates the regular payment. If B1 contains the…
- Excel AVERAGEIF Formula Examples: Criteria, Ranges, FixesUse Excel’s AVERAGEIF function to average values that meet one condition: =AVERAGEIF(A2:A6,"North",B2:B6). If A2:A6 contains regions and B2:B6…
- Excel Formula to Return Text Left of a Character in a CellTo return the text to the left of a character in Excel, use =LEFT(A2,FIND("-",A2)-1), replacing A2 with the…
- How to Copy Formula Data in Excel Without Breaking ReferencesTo copy formula data in Excel, select the cells, press Ctrl+C, choose the destination, and use Home >…
- Excel Accounting Shortcuts: Formulas, Keys, and TipsExcel accounting shortcuts are a mix of keyboard commands and formulas: use Alt+= to insert an AutoSum, Ctrl+T…
- Excel LET: Can It Use Multiple Variables?Yes. Excel’s LET function allows multiple named values in one formula: write each name and value as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.