Highlight Rows Between Two Dates with Conditional Formatting in Excel

Bottom Line: Learn to apply conditional formatting for entire rows based on two dates in Excel. Highlight entries within or outside of a date range.

Skill Level: Advanced

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

You can download the file that I use in the video in order to follow along.

Highlighting Rows Based on a Condition

This is the second post in a series about formatting entire rows based on conditions. I recommend you check out this prior post first to get a better understanding of how you can use the data in single cell as the basis for conditionally formatting an entire row:

How to Apply Conditional Formatting to Rows Based on Cell Value

Highlighting Entries Based on Dates

Building on the concept of formatting rows based on cell data, today's post will describe how to highlight rows based on two dates. You can format rows that have an entry between or outside of any two given dates so that they stand out in your table. Here's an example of a sheet that formats rows containing a date anywhere between January 1, 2018, and December 31, 2018.

Conditional Formatting Between Dates Example

The formatting is both automatic and dynamic. This means that if you change the Start or End Date, the table will instantly update to reflect the change.

Between Two Dates

To highlight a row that has a cell that is between two dates, we are going to use the AND function.

The AND function has arguments for two or more logical tests. Each logical test must return a True or False value. This is usually done with comparison operators (=, <, >, <>).

Our first argument will say that the date in the cell we choose must be greater than or equal to (>=) our start date. In our example this looks like C7>=$C$3. (The dollar signs denote that this is an absolute value that won't change when changes are made to the worksheet.)

Our second argument is that the date should be less than or equal to (<=) the end date. C7<=$C$4.

Putting it all together, the formula for our example is: =AND (C7>=$C$3, C7<=$C$4)

AND formula

Applying the Conditional Formatting

Now that we see how the formula is written, we will apply the formatting. Here are the steps to do that.

  1. Select the cell in the first entry of the date column. In my example worksheet, that's column C.
  2. On the Home tab of the Ribbon, select the Conditional Formatting drop-down and click on Manage Rules…. That will bring up the Conditional Formatting Rules Manager window.
  3. Click on New Rule. This will open the New Formatting Rule window.
  4. Under Select a Rule Type, choose Use a formula to determine which cells to format.
  5. Under Format values where this formula is true, you are going to write the formula that we created in the section above: =AND ($C7>=$C$3, $C7<=$C$4)
  6. When filling in the values while writing the formula, Excel may automatically put the absolute reference indicators in front of C7, so that it looks like $C$7. To ensure that the conditional formatting applies to all of the rows in the table, we need to change the absolute relative referencing. In other words, we are going to remove that dollar sign in front of the row number (7) in our formula. You can either manually delete it, or you can hit F4 twice to accomplish this step.

    This means the formula will always use column C for the comparison, but the row number will change as the formula is evaluated in each row of the table. Check out my previous article on conditional formatting to learn more about how the rules are applied.
  7. Click on the Format… button to choose whatever format options you like. You can change the font, the fill, the border, etc.

By hitting OK, you'll be taken back to the Conditional Formatting Rules Manager. The only change we want to make here is to include the entire table, not just the cell we started from.

To do that, click on the icon to the right of the Applies to field (it has an upward facing arrow) and select the range of the entire table. In the example, this would be the data ranging from B7 to G1003 (=$B$7:$G$1003).

Conditional Formatting Rules Manager

Outside Two Dates

We've looked at how to identify numbers that fall between two dates, but what if we want to highlight the opposite? We can use the OR function to draw attention to dates that fall outside of a date range (before the start date or after the end date).

The OR function checks whether any of the arguments are true, and if one of them is true, it returns TRUE. They don't have to all be true. Only if all of the arguments are false will the function return FALSE.

Similar to the AND function in the way that it's written, the OR function instead looks for anything less than or equal to (<=) the start date OR greater than or equal (>=) the end date.

So for our example, we will use =OR($C7<=$C$3, $C7>=$C$4)

Edit Formatting Rule

Once that rule is applied, any entry that falls outside of the specified date range will be formatted to stand out.


Both of these techniques also work with regular numbers, not just dates, so keep that in mind.

If you want to read more about AND and OR functions, check out this tutorial as well: IF Function Explained: How to Write an IF Statement Formula in Excel.

I hope this is helpful to you as you build reports designed to call attention to entries that fall within or outside of particular data ranges. If you have any questions or comments, I'd love to hear them in the comments section below.

Until next time!


Your email address will not be published. Required fields are marked *

  • Hi Jon – Great video, but don’t you need to remove the = in the OR conditional formatting ie change >= to > and <= to < if you want to not include the start and end dates

  • Jon, quick question on using dates. In your example above you are referencing a cell in which the date is present (cell C3 & C4). Then you use the formula =AND (C7>=$C$3, C7=”1/1/2018″,C7 <="12/31/2018") doesn't work, but is there someway with other syntax to do this?


    • Mr. Ken
      I think the problem in your formula is here: ,C7 <="12/31/2018")
      the comparison here will be based on text not on date.
      maybe changing that to cell reference will solve the problem.

  • Thank you. Very helpful.
    I suggest a subject for a new post “Invert if Negative” in charts
    I think this option is not working properly, especially, on pivot charts.

  • I am wondering why the filter function in EXCEL does not allow us to us Boolean values to search on values in a column. For example: if I want to find 3 values in a column, I would think I could add this to the search column in the filter dropdown like this:
    Value1 OR Value2 OR Value3
    But instead it looks like I have to paste the values in a new column and use conditional formatting.

  • This helps tons. I am just wondering if you had a walk through on how you did the column drop downs? I.E the region, customer, and such. I can’t seem to figure out how to do that properly myself and could use some help.

  • can anyone help me on some formula for my requirement
    Sheet 1 I have 20 rows with name of employee in each row and 4 columns Column 1 Month / column 2 date, column 3 Month / column 4 Date (Column 1,2 is FROM / column 2,4 is TO)
    Following 12 sheets for 12 months (Each month 1 sheet with 20 rows with name of same employees in sheet 1). Row is employee name and column is date (just date number 1,2,3…..31)
    Now based on date I enter for employee Mr. X (e.g. Jan 5 Feb 2) It should colour cell Mr. X in Jan sheet from 5th to Feb sheet 2nd

    Can anyone guide me for the formula

  • Hi,
    first of all thanks for sharing such useful info. but i have different condition. “If cell contains any date then highlight entire row”.

    Thanks in advance 🙂

  • Hello, thanks for the video. I have something similar with a small twist. I am trying to highlight a column called ‘Job Date’ if the date falls between the “Date of Visit” and “+45days.” So my comparison dates keep changing. For example, I visited customer A on 9/1/20, and got a job on 10/20/20 and I visited customer B on 9/15/20 and got a job from them on 10/20/20 as well. I want to highlight the job date if it is within 45 days of the visit. Thanks in advance.

  • Hi Jon,

    I am looking to highlight only individual cells not an entire row. I followed your steps and would love guidance on how to only highlight individual cells in the table.

  • Hi Carl,
    I am working on a Cashflow and need to format the amounts of the dates that are now actual from the forecasted cashflow numbers, this is a daily cashflow sheet. Can you help

  • Hello, I need help with understanding why the conditional formatting I am applying to an exported document is not functioning. Here is my situation: I run the supply of an operating room and have cataloged all of our consumable supplies into an Android App called, “Stock and Inventory Online”. This app allows to have cataloged goods exported as an Excel document. I have used the Details section of each item to state the soonest expiration date and then I am exporting this information to its own unique Excel column.

    The conditional formatting that I then apply is as follows: SELECT the column, CLICK Conditional Formatting then New Rule…, SELECT Format only cells that contain, SELECT Less than, TYPE =TODAY(), SELECT Format…, and then I am having it fill the cell(s) Red. After selecting OK none of my data that should meet those requirements changes. However, if I select into a cell that should have changed and proceed to as if I was going to edit within that cell but simply press Enter, then the formatting applies as it should. I have tried ensuring that the column is defined as a Number, Date, General and have done so before and after applying the conditional formatting without any success. I have also tried to copy and move the data to a different column with the same formatting applied before and after copy/move without success.

    If anyone thinks they can help I would be happy to send the document.

  • I have a table with dates (several years) and weight. For every year I want to highlight the highest and lowest weight.
    When I create a conditional formatting rule for the whole table it only highlights 2 rows in total. But I want to apply it by year.
    Question: can you make 1 rule and apply it to more ranges in the same table?

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter