Conditional Formatting in Excel for a Specific Date

How-to basics · Updated November 22, 2026

Yes. In Excel for Microsoft 365 on Windows, select the date cells and use Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With dates in A2:A100 and A2 as the first selected cell, enter =AND(ISNUMBER($A2),INT($A2)=DATE(2026,11,3)) to highlight November 3, 2026, including entries that also contain a time. Choose a format and select OK.

What “a specific date” means in Excel

Excel stores recognized dates as serial numbers, not as words. The cell’s number format controls how that number appears—for example, as 11/3/2026 or November 3, 2026. That distinction matters because conditional formatting compares the underlying value, not the way the date is displayed. Two cells can look the same but contain different values if one has a hidden time or is stored as text.

A rule for a specific date is different from a rule for a changing period such as today, yesterday, or next week. Excel’s built-in date-occurrence rules are convenient for relative periods, while a formula rule provides more control over a fixed date, a date stored in another cell, or dates that include times.

  • Fixed date: Highlight every entry for one calendar day, such as a deadline or event date.
  • Reference date: Highlight dates that match a date typed into a designated cell, so the target can be changed without rebuilding the rule.
  • Date range: Highlight all dates from a start date through an end date, such as a reporting period.

Use the built-in Equal To rule

For a simple list of dates with no time values, Excel can compare cells with a date entered directly through a built-in rule. Select the cells to format, then choose Home > Conditional Formatting > Highlight Cells Rules > Equal To. Enter the target date in the dialog, choose a formatting style, and confirm. Excel applies the rule to the selected range.

Enter a date Excel recognizes, rather than a date written ambiguously as text. The way Excel interprets entries such as 03/11/2026 can depend on regional settings: it may mean March 11 or 3 November. A less ambiguous entry, such as 3-Nov-2026, can help when typing the value, but the cell still needs to be recognized as a date. Check the formula bar or temporarily apply a date number format if you are unsure.

The built-in Equal To rule is quick, but a formula is usually more flexible. For example, the formula method makes it easy to ignore time portions, use a date in another cell, or highlight a range of dates with one rule. It also makes the row-reference logic visible when you apply formatting across multiple columns.

Create a formula rule for an exact date

First select the cells the rule should evaluate. If the dates are in A2:A100, select that range. Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, enter the formula, choose Format to set the fill, font, or border, and select OK. The formula must return TRUE for a cell that should receive the format.

With A2:A100 selected and A2 as the top cell, use =A2=DATE(2026,11,3) to match the fixed date November 3, 2026. The DATE function takes year, month, and day in that order, so the formula avoids ambiguity from regional date-entry formats. In a conditional-formatting formula, the row reference changes for each cell in the selected range: Excel checks A2, then A3, then A4, and so on.

The opening formula adds two safeguards. ISNUMBER checks that the cell contains a numeric date value rather than text, and INT removes any fractional time portion before comparing the calendar date. With A2:A100 selected and A2 at the top of the selection, =AND(ISNUMBER($A2),INT($A2)=DATE(2026,11,3)) highlights a value such as November 3, 2026 at 2:30 p.m. It does not highlight a text string that merely looks like that date.

In the formula, the dollar sign before A fixes the column while leaving the row relative. That is helpful when the rule applies across a wider row, because every cell in a row can be formatted based on the date in column A. If you select only A2:A100, the column lock is optional, but it makes the intended reference clear.

Use a date in another cell as the target

A reference cell is useful when the target date changes frequently. For example, enter the date to search for in D1, then make a conditional-formatting rule for the date list. Format D1 as a date so it is easy to read, and ensure it contains a real Excel date value rather than text.

With dates in A2:A100, D1 holding the target date, and A2 as the first selected cell, use =AND(ISNUMBER($A2),INT($A2)=INT($D$1)). The dollar signs make D1 an absolute reference, so every row compares against the same target. Changing D1 updates the highlights automatically; you do not need to edit the conditional-formatting rule.

Using INT on both values is appropriate if the target cell might also contain a time. If you know D1 is always a date with no time, comparing INT($A2) directly with $D$1 is also suitable. Avoid placing the target date inside the range being formatted unless that is intentional, because a changed cell value can then affect both the comparison and the appearance of the list.

Highlight a date range or a particular day each year

To highlight dates between two endpoints, put the start date in D1 and the end date in E1. With dates in A2:A100, D1 and E1 containing real dates, and A2 as the first selected cell, use =AND(ISNUMBER($A2),INT($A2)>=$D$1,INT($A2)<=$E$1). This includes both endpoint dates. If the start or end should not count, change the corresponding comparison from greater-than-or-equal or less-than-or-equal to a strict comparison.

For a repeating annual event, such as a birthday, the year may not matter. With dates in A2:A100 and A2 at the top of the selected range, use =AND(ISNUMBER($A2),MONTH($A2)=11,DAY($A2)=3) to highlight November 3 in any year. This checks month and day independently, so it can also match February 29 where present. Decide whether that behavior is suitable for the event or reporting rule you need.

Be clear about inclusive dates when defining a period. A “through November 30” rule usually includes November 30; a rule intended to stop before that day should use a strict upper comparison. For reporting periods that are described as dates but whose data includes times, using INT to compare calendar days prevents a late-afternoon timestamp from slipping outside the intended final day.

Apply the rule to full rows

Often the date is just one field in a table, and the goal is to color the entire record. Suppose dates are in column A and the table spans A2:F100. Select A2:F100, then create a formula rule that refers to the date column. With A2 as the top-left row reference, use =AND(ISNUMBER($A2),INT($A2)=DATE(2026,11,3)). The column is fixed so every cell in a row checks column A; the row remains relative so each record is tested against its own date.

The selection determines where formatting can appear. If you select only A2:A100, only the date cells will be colored even if the formula is correct. To color each full row, select the full table range before creating the rule. In Conditional Formatting > Manage Rules, review the Applies to field to confirm the range is what you intended. If the table grows, you may need to extend that range, or use an Excel table so its formatting range can expand with added records.

Fix common date-matching problems

If a cell that looks like the target date is not highlighted, check what Excel actually has in the cell. A date imported from a website or text file may be stored as text. Applying a date number format does not convert text into a date; it only changes the display of numeric values that Excel recognizes. You may need to convert the imported values to dates before the rule can evaluate them as dates.

  • Check for a time: A value displayed as 11/3/2026 may contain an unseen time. Use the INT-based exact-date formula to compare the calendar day.
  • Check for text: If ISNUMBER returns FALSE for a supposed date, convert the source value. Re-entering a date or using Excel’s data-conversion tools may be appropriate depending on how the data was imported.
  • Check the selected range: The formula’s relative row should correspond to the first row of the Applies to range. If the range begins at row 5, the formula should normally reference row 5, not row 2.
  • Check rule order and formatting: Another conditional-formatting rule may use a conflicting format or, if Stop If True is enabled, prevent a later rule from being evaluated.
  • Check the target cell: A cell used as a reference should hold a real date and should not be blank. Confirm that its displayed date matches the intended day.

To inspect or adjust rules, use Home > Conditional Formatting > Manage Rules. In the rules manager, choose whether to show rules for the current selection or the worksheet, then check the formula and Applies to range. Rule priority matters when multiple rules target the same cells. Conditional formatting changes appearance only; it does not change the cell’s date, convert text, or alter calculations.

Excel for Mac and Excel for the web

The core formula approach works in current Excel for Microsoft 365 on Windows and Mac. On Mac, use the Home tab’s Conditional Formatting controls and create a formula-based rule; the dialog layout may look different, but the formula logic and relative-reference behavior are the same. Excel for the web also supports conditional formatting, though some rule-management options and dialog details can differ from the desktop application.

Keyboard shortcuts can speed up selection, but there is no need to use one to create the rule. In Windows Excel, Ctrl+1 opens Format Cells, which controls ordinary number formats; it does not open the conditional-formatting rule editor. On a Mac, Command+1 opens Format Cells. For conditional formatting itself, the ribbon path is the clearest route and helps avoid confusing a display format with a rule that applies only when a condition is true.

Frequently Asked Questions

Can I highlight today’s date automatically?
Yes. Use a relative-date option such as Today under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. The rule updates as the current date changes. If your cells include times, use a formula rule that compares INT of the date cell with TODAY, and make sure the selected range and row reference align.

Why does Equal To not match a date that looks identical?
The cell may contain a time, text rather than a numeric date, or a different underlying year that is hidden by the display format. Compare the actual values, or use a formula with INT to ignore times. If the value is text, convert it to a real Excel date before relying on date comparisons.

Can I highlight dates in a whole row?
Yes. Select the entire table range before creating the rule, then write the formula to refer to the date column with an absolute column and relative row, such as $A2 when row 2 is the first selected row. Check Applies to in Manage Rules to ensure the full rows are included.

Can I highlight a date in one cell without changing the other dates?
Yes. Conditional formatting changes only the appearance of cells that meet the rule. It does not edit the date values. Select just the date cells if you want only those cells colored, or select the full record range if you want the corresponding rows formatted.

Does Excel’s A Date Occurring rule let me enter any fixed date?
That built-in rule is intended for relative periods such as yesterday, today, or next month, rather than specifying an arbitrary fixed calendar date. For a fixed day, use Highlight Cells Rules > Equal To or create a formula-based rule with DATE.

Can the rule highlight the same month and day across different years?
Yes. Use a formula that tests MONTH and DAY rather than comparing the full date value. This is useful for recurring dates, but consider special cases such as February 29 before applying it to a schedule with different policies for non-leap years.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.