To highlight dates between two limits in Excel, select the date cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and use =AND(ISNUMBER($A2),$A2>=$E$1,$A2<=$F$1). This example checks dates in column A against an inclusive start date in E1 and end date in F1; choose a format, then select OK.
Set up the rule
In Excel for Microsoft 365 on Windows, first arrange the dates and boundaries. For example, put the dates to check in A2:A100, the beginning of the period in E1, and its end in F1. Enter actual Excel dates in E1 and F1, not date-looking text. Select A2:A100, then open Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
With dates in A2:A100 and the boundary dates in E1 and F1, enter =AND(ISNUMBER($A2),$A2>=$E$1,$A2<=$F$1) in the formula field. Select Format to choose a fill, font, border, or number format, then confirm the rule. The dollar signs keep the date column and boundary cells fixed as Excel evaluates each row. The row number in $A2 is deliberately not fixed, so the rule checks A2, then A3, A4, and so on.
The result includes both the start date and the end date. If A2 contains the start date or a later date up to and including the end date, the selected formatting appears. Dates outside that interval, blank cells, and non-date text are not highlighted by this formula.
What the formula checks
Excel stores genuine dates as serial numbers and displays them using a date format. The formula uses AND to require several conditions to be true at once: ISNUMBER confirms that the value in the date cell is numeric, the first comparison checks that it is on or after the start, and the second checks that it is on or before the end. This numeric test is useful because empty cells and imported text can otherwise behave unexpectedly in comparisons.
The references are designed for a selected range beginning at row 2. The $A locks the column, which matters if you later apply the rule across multiple columns; the unanchored 2 lets the row adjust. The $E$1 and $F$1 references lock both the boundary column and row, so every date is compared with the same two limits. If your selected range begins at row 5 instead, use a formula whose date reference starts with row 5, such as $A5.
For a quick check, put 10/01/2026 in E1 and 10/31/2026 in F1, using a date format appropriate to your regional settings. A date of 10/15/2026 in A2 passes all three tests and is highlighted. A date of 11/01/2026 fails the end-date test. The exact way a date is typed can vary by region, so verify that Excel recognizes it as a date rather than relying on how the text looks.
Highlight an entire row based on its date
You can use the date in one column to format a complete record. Suppose each row contains a date in column A and other information in columns B through F. Select A2:F100 before creating the formula rule. With dates in A2:A100 and boundaries in E1 and F1, use =AND(ISNUMBER($A2),$A2>=$E$1,$A2<=$F$1). Choose a fill color, and Excel will apply it across the selected row whenever the date in column A is in range.
Here, $A2 is important: it tells Excel to always look in column A while allowing the row to change. If you wrote A2 without a dollar sign before A, the reference could shift sideways as the rule is evaluated across columns. If you fixed the row as $A$2, every row would be judged using only the date in A2. Check the Applies to field in Conditional Formatting Rules Manager if the wrong cells change; it should cover the rows and columns you intend to format.
Dates that include times
A date-time value contains a date and a time fraction. If your data includes times, the inclusive formula using <=$F$1 may exclude entries on the final day after midnight. For example, if F1 is October 31 at midnight, an entry at October 31 at 3:00 p.m. is later than that boundary, even though it is on the same calendar day.
To include every time on the end date, treat the end as the start of the following day and use a less-than comparison. With date-time values in A2:A100 and the start and end dates in E1 and F1, use =AND(ISNUMBER($A2),$A2>=$E$1,$A2<$F$1+1). If F1 contains October 31, this admits values before November 1 at midnight, including times throughout October 31. This approach assumes E1 and F1 are date boundaries; it is not the right choice if the end limit is an exact time that should be enforced.
For a period that starts and ends at precise times, keep the direct comparisons and enter the full date-time values in the boundary cells. In that case, the inclusive rule compares the complete values, not just their displayed date portions. Cell formatting can hide times, so select a cell and inspect the formula bar or temporarily apply a date-and-time number format when checking unexpected results.
Built-in date rules versus a formula
Excel also offers date-oriented choices under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. Those presets are convenient for relative periods such as yesterday, today, tomorrow, last week, or next month. They are not the same as specifying any two custom boundary cells. For a fixed or user-controlled interval such as E1 through F1, a formula rule is the more flexible option.
Another alternative is to filter a table by date when the goal is to show only records in a period, rather than visually mark them. Filtering changes which rows are displayed; conditional formatting leaves the full list visible and adds a visual cue. A normal sort or filter does not replace a formatting rule, and the rule does not remove or hide values.
Choose a fill or font treatment that remains readable alongside existing formatting. Conditional formatting changes appearance, not the underlying date, so the value can still be sorted, filtered, calculated, or copied. If several rules affect the same cells, their order and settings in Conditional Formatting Rules Manager can influence the final appearance. Keep rules focused and remove obsolete ones to make the workbook easier to maintain.
Common problems and how to fix them
- Nothing is highlighted. Check that the values are real dates and that the start boundary is earlier than or equal to the end boundary. Text imported from a CSV or copied from a website may look like a date without being stored as one. Test a cell by changing its number format; if it does not behave like a date serial, convert the source data before relying on comparisons.
- The wrong rows are highlighted. Match the formula row to the first row in the selected range. If the Applies to range starts at A2, the formula should evaluate row 2. A formula beginning with $A1 would test the preceding row and create an apparent offset.
- Only one cell changes when an entire row should change. Select the full intended area, such as A2:F100, before making the rule. A rule only formats cells covered by its Applies to range, even when its formula refers to a date elsewhere in each row.
- Dates appear to be one day out. Regional date conventions can interpret entries such as 03/04/2026 differently. Use an unambiguous entry method for your locale, inspect the displayed date, and confirm the workbook recognizes the value. Avoid comparing dates stored as text with genuine date serials.
- The last day is missed. If records contain times, use the less-than-next-day formula for whole-calendar-day boundaries. If records are dates without times, the inclusive less-than-or-equal formula is usually the straightforward choice.
- The formatting appears inconsistent. Open Home > Conditional Formatting > Manage Rules, review the selected worksheet in the Show formatting rules for list, and inspect Applies to and rule order. Other conditional-formatting rules may overlap the same cells.
Keep the rule reliable as data changes
For a short fixed list, a range such as A2:A100 is easy to understand. If rows are frequently added, consider converting the dataset to an Excel table with Insert > Table, then make sure the conditional-formatting rule covers the table’s data rows. After adding records, check Manage Rules to confirm the Applies to range includes them; do not assume a rule has expanded correctly in every workbook layout.
Keep the boundary dates in clearly labeled cells, for example Start date in D1 and End date in E1, rather than leaving unexplained numbers in a worksheet. Update those cells to change the highlighted period without editing the rule. If users can enter the boundaries, data validation or clear instructions can help prevent an end date earlier than the start date. Conditional formatting itself will simply find no values in a reversed interval; it does not automatically warn that the inputs are reversed.
In Excel for Mac, the same formula logic works. The ribbon is also organized around Home and Conditional Formatting, although dialog details and labels can vary slightly by release. Excel for the web supports conditional formatting, but the available editing controls can differ from the desktop dialog. If a workbook is shared across platforms, confirm the rule and its Applies to range in the version where the workbook will be maintained.
Frequently Asked Questions
Can I highlight dates between two dates in Excel without a formula?
For built-in relative periods, use Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. To compare against two custom dates stored in cells, a formula rule is generally needed; it can update automatically whenever either boundary date changes.
Does the rule include the start and end dates?
Yes. The comparisons >= start and <= end include both boundary dates when the cells contain dates without times. If entries include times on the final day, use the less-than-end-plus-one approach to include that whole calendar day.
Can the start and end dates be typed directly into the formula?
It is usually easier to keep them in worksheet cells, because you can change the period without revising the rule. Excel date literals embedded in formulas can be less readable and harder to maintain; cell references make the boundaries visible to other workbook users.
Why does a blank date cell sometimes get highlighted?
Comparisons with blank cells can produce results that are surprising, particularly when the range includes unusual boundary values. Adding ISNUMBER around the date-cell reference makes the rule require a numeric date or date-time value before it can format the cell.
Can conditional formatting highlight the whole row?
Yes. Select the entire record range before creating the rule, and use a date reference that locks the date column but leaves its row relative, such as $A2. Each row then receives formatting based on its own date in column A.
Related
- Excel Conditional Formatting Number Formats Explained
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Keyboard Shortcuts for Selecting Cells QuicklyIn Excel for Microsoft 365 on Windows, use Shift+Arrow to extend a selection one cell at a time,…
- Excel Protect Sheet vs Protect Workbook: Key DifferencesProtect Sheet controls what people can change on an individual worksheet; Protect Workbook protects the workbook’s sheet structure,…
- Format Zero as a Dash in Excel with Custom FormattingTo show numeric zeros as dashes in Excel without changing the underlying values, select the cells, press Ctrl+1,…
- Removing Duplicates in Google Sheets: A Practical GuideTo remove duplicate rows in Google Sheets, select your table, choose Data > Data cleanup > Remove duplicates,…
- Corrupted Excel File Repair: What to Try FirstFor a corrupted Excel file, first make a copy of the file, then try Excel’s built-in Open and…
- How to Schedule an Email in Outlook Web App for LaterTo schedule an email in Outlook on the web, compose your message, select the arrow beside Send, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.