To conditionally format dates within a range in Excel, select the date cells and choose Home > Conditional Formatting > Highlight Cells Rules > Between for fixed start and end dates. For a range controlled by dates in worksheet cells, create a formula-based rule so the highlighted dates can update when those boundary dates change.
The quickest way to highlight a date range
For a one-time comparison, Excel’s built-in Between rule is the most direct option. It highlights values at or above the lower boundary and at or below the upper boundary, so both endpoints are included. This works well for a short report where the dates are unlikely to change.
- Select the cells containing the dates, such as A2:A100. Select only the date cells if you want to color dates but not the rest of each row.
- On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Between.
- Enter the first and last dates in the two boxes. Use dates Excel recognizes, such as dates entered in the same format as the worksheet, rather than ambiguous text.
- Choose a preset format, or select Custom Format to set a fill, font, border, or number format, then confirm the rule.
Excel applies the format to cells whose values fall within the selected interval. To change the interval later, use Conditional Formatting > Manage Rules, select the rule, and edit its limits. Menu labels are similar in Excel for Mac, though the dialog layout can differ. Excel for the web also has conditional formatting controls, but the available editing experience may vary from the Windows desktop application.
How Excel stores dates
Excel normally stores a date as a number representing a day in its date system, then displays that number using a date format. For example, a cell that looks like a date can be compared numerically with another date. Conditional formatting checks the underlying value, not whether the cell merely looks date-like.
This distinction explains a common failure: imported dates may be text rather than real Excel dates. A value such as 2026-01-15 can appear correctly on screen but fail a date comparison if it was imported as text. Changing the number format alone does not convert text into a date. If a date will not behave as expected, test whether Excel recognizes it as a number, and convert or re-import text dates as actual dates before relying on the rule.
Blank cells and text values can also affect rules. A formula rule can explicitly require a numeric value, which helps avoid coloring empty cells or notes in a mixed range. This is especially useful when a worksheet is still being filled in.
Use a formula for a changing date range
A formula-based rule is better when the desired period is controlled by worksheet cells, such as a report start date and end date. It is also useful when someone needs to change the reporting period without opening and editing the conditional-formatting rule itself.
Assume A2:A100 contains the dates to format, E1 contains the start date January 1, 2026, and F1 contains the end date January 31, 2026. Select A2:A100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter this formula:
=AND(ISNUMBER(A2),A2>=$E$1,A2<$F$1+1)
Choose a format, confirm the rule, and check that the Applies to range is A2:A100. With the example dates, a valid date on January 1 or January 31 is highlighted, a date on February 1 is not, and a blank cell is not highlighted. The ISNUMBER check requires the tested cell to contain a numeric date value.
The references are intentional. A2 is relative, so as Excel evaluates the rule down the selected range, it checks A3, A4, and the following cells. The dollar signs in $E$1 and $F$1 keep the boundary references fixed. If you select a different range, make sure the first cell reference in the formula matches the top-left cell of that selection; otherwise, Excel can evaluate the wrong row.
Include the whole end date and handle times
A date cell can contain a time as well as a date. For example, a value displayed as January 31 may actually represent January 31 at 3:00 p.m. A comparison that uses less than or equal to the end-date cell can exclude such values if the end-date cell represents midnight at the beginning of January 31.
The formula above avoids that problem by checking whether a value is earlier than one day after the end date. Since Excel date values advance by one for each day, adding 1 to F1 sets the boundary at midnight on February 1. This includes all times on January 31 while excluding February 1. The formula assumes E1 and F1 contain dates, not timestamps with meaningful times of their own.
If your data is guaranteed to contain dates with no time component, simpler comparisons can be sufficient. If it comes from a system that records appointment or transaction times, using the next-day exclusive boundary is a safer way to include the full final calendar day.
Choose the right kind of date rule
Excel offers several approaches, and the best one depends on whether your period is fixed, moves with today’s date, or is chosen by a user.
- Fixed dates: Use Highlight Cells Rules > Between when the boundaries are known and do not need to be driven by worksheet cells.
- A moving period around today: Use Conditional Formatting > Highlight Cells Rules > A Date Occurring for built-in choices such as yesterday, today, tomorrow, last 7 days, this week, or next month. These rules are useful for quick visual indicators.
- User-selected dates: Use a formula-based rule with start and end dates in cells. Changing those cells updates the comparison without rebuilding the rule.
- More complex criteria: Use a formula rule if dates must also meet another condition, such as belonging to a particular category or being in an unfinished record.
A Date Occurring rules are convenient, but they are not the same as a custom interval chosen in two cells. Their built-in periods are based on the current date and the option selected, whereas cell-driven bounds let a user choose a specific reporting window. Pick the simplest approach that matches the job, since a simpler rule is generally easier to review later.
Common problems and fixes
If dates within the range are not highlighted, first check the Applies to range in Manage Rules. A rule can be correct but attached to the wrong cells. Also verify that the formula’s first cell reference corresponds to the top-left cell of the Applies to range, and that the start and end cells contain real dates in the correct order.
- Some dates are ignored: Check whether those entries are stored as text, contain hidden spaces, or include times. Convert text dates to real date values before testing the rule.
- Unexpected cells change color: Review the Applies to range and relative references. A shifted starting reference can make each row compare against a neighboring row.
- Blank cells are colored: Use a numeric-value test in a formula rule, or make sure the selected range does not include unwanted blanks.
- The rule works until the worksheet changes: Confirm the range includes newly added rows. A rule applied to A2:A100 will not automatically cover data entered in A101 unless the range is extended or the data is in an Excel table that expands with new records.
- The displayed dates look different: Conditional formatting changes appearance; it does not repair a stored value or necessarily change its date display format. Check the cell value and number format separately.
For a formula-based rule, Excel expects a formula that evaluates to TRUE or FALSE for each cell. A valid formula can still produce unexpected results if a boundary cell is blank, contains text, or has a time component the rule was not designed to handle. Keep the input cells clearly labeled and use actual date values to make the worksheet easier to maintain.
Manage rules and keep a workbook clear
When several conditional-formatting rules affect the same cells, use Home > Conditional Formatting > Manage Rules to inspect them. The dialog lets you review the rule description, its Applies to range, and its order. In cases where multiple rules could apply, rule order and the Stop If True setting can affect which formatting is shown. If a color seems inconsistent, inspect the rules for that range rather than creating another rule immediately.
Use a clear format that communicates the date condition without obscuring the date itself. A light fill or a distinct font color is often easier to read than several competing colors. If dates are copied into another part of the sheet, verify the copied rule’s range and references; a copied conditional format may adjust relative references, which can be helpful or may need correction.
For recurring reports, a small labeled input area for the start and end dates makes the rule understandable to other people. It also avoids hard-coding a period into the rule. Keep the input cells as dates and label them plainly, such as Report start and Report end, so users know what they can change.
Frequently Asked Questions
Can Excel highlight dates between two dates?
Yes. Select the date cells and use Home > Conditional Formatting > Highlight Cells Rules > Between. Enter the lower and upper dates and choose a format. For boundaries stored in worksheet cells, use a formula-based rule instead.
Can I highlight dates from today through the next 30 days?
Excel’s A Date Occurring menu provides several ready-made periods, but it may not match every custom window. For a specific rolling period such as today through the next 30 days, use a formula-based rule with an appropriate start and end condition, or use cells to hold the boundaries if users need to adjust them.
Why does conditional formatting skip a date that looks correct?
The displayed entry may be text, or it may include a time value that falls outside the comparison. Check that Excel recognizes it as a date value, then account for times if the range should include the entire final day.
Does Between include the start and end dates?
Yes. The built-in Between rule treats both limits as included. In formula rules, use inclusive comparisons for the start and take care with the end boundary when the date values may contain times.
Can I highlight an entire row based on its date?
Yes. Apply a formula-based rule to the rows you want to format and reference the date column so it stays fixed while the row changes. Set the Applies to range to the full rows or columns of cells to be formatted, and align the formula’s row reference with the first row of that range.
Related
- Calculate the Middle Date Between Two Dates in Excel
- Calculate Average Time Between Dates in Excel with AVERAGE
- Budget vs Actual Excel Template: Build and Use One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Absolute Cell Reference Excel Shortcut: F4 ExplainedIn Excel for Microsoft 365 on Windows, press F4 while editing a formula and the insertion point is…
- How to Turn On Track Changes in Microsoft Word DesktopTo turn on Track Changes in Word desktop, open the document, select Review > Track Changes, and then…
- Excel Find and Replace Carriage Returns: Steps and FixesIn Excel for Microsoft 365 on Windows, press Ctrl+H, click the Find what box, and press Ctrl+J to…
- How to Delete a Blank Page in Word Without Losing TextTo delete a blank page in Word, turn on formatting marks with Ctrl+Shift+8 on Windows or Command+8 on…
- Conditional Formatting Between Two Values in ExcelIn Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home >…
- Out of Office in Outlook on iPad: Set Automatic RepliesTo set an out-of-office reply in Outlook on an iPad, open Outlook’s settings, select the email account, tap…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.