To apply conditional formatting to cells that are between two specific dates in Excel, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.
The following example shows how to use this option in practice.
Example: Apply Conditional Formatting if Cell is Between Two Dates
Suppose we have the following dataset in Excel that shows the number of sales made on various dates at some store:
Now suppose that we would like to apply conditional formatting to each row in the dataset that has a date between 1/15/2023 and 2/15/2023.
First, we can define these start and end dates in cells E1 and E2, respectively:
Next, highlight the cells in the range A2:B11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
In the new window that appears, click Use a formula to determine which cells to format, then type =AND($A2>=$E$1,$A2<=$E$2) in the box, then click the Format button and choose a fill color to use.
Once we press OK, all of the rows in the dataset that have a date between 1/15/2023 and 2/15/2023 will be highlighted:
If we change the start or end date to a different date, then the conditional formatting rule will automatically adjust to highlight the correct cells.
For example, suppose we change the end date to 3/15/2023 instead:
Now all rows with a date between 1/15/2023 and 3/15/2023 are highlighted.
Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Apply Conditional Formatting if Cell Contains Text
Excel: Apply Conditional Formatting with Multiple Conditions
Excel: Apply Conditional Formatting if Between Two Values
Hey Zach, Wondering if you can help. All the Excel examples I read for conditional formatting dates assumes the date series runs down the page. I have a calendar I have made for easy view of the 12 months. I am wanting to create functionality where when a team member puts a start date and end date on separate booking sheet, the corresponding date cells in the calendar will then highlight (conditionally format). So for example, the dates for January 2025 run in cells E9:AI9. If a start date is entered as 15/01/2025 and and end date of 25/01/2025 I want those cells in the calendar to highlight. I also need this to be defined by these dates entered in cells on the next sheet rather than via the conditional formatting drop down menu (eg I need the formula). Does this make sense? Can you help?
Hi Adrian…Yes, this can definitely be done! You need to use a formula-based conditional formatting rule that references the start and end dates entered in the separate booking sheet. Here’s how you can set it up:
—
### Steps to Apply Conditional Formatting:
1. **Understand the Layout**:
– Your calendar has the dates for January 2025 in cells `E9:AI9`.
– The start and end dates are entered on a separate sheet (let’s call it `Bookings`) in specific cells, say `Bookings!A2` for the start date and `Bookings!B2` for the end date.
2. **Write the Conditional Formatting Formula**:
– The formula will check if the date in each cell falls between the start and end dates.
3. **Apply the Formula**:
– Select the range `E9:AI9` in your calendar.
– Go to **Home** > **Conditional Formatting** > **New Rule** > **Use a formula to determine which cells to format**.
– Enter the formula:
“`excel
=AND(E9>=Bookings!$A$2, E9<=Bookings!$B$2) ``` - Replace `E9` with the first cell in your selected range. - Format the cells as desired (e.g., fill with a specific color). - Click **OK**. 4. **Explanation of the Formula**: - `E9>=Bookings!$A$2`: Checks if the date in `E9` is on or after the start date.
– `E9<=Bookings!$B$2`: Checks if the date in `E9` is on or before the end date. - `AND(...)`: Ensures both conditions are true before applying the format. 5. **Test the Conditional Formatting**: - Enter start and end dates in the `Bookings` sheet and verify that the corresponding cells in the calendar are highlighted. --- ### Adjusting for Multiple Bookings: If multiple bookings are entered in rows on the `Bookings` sheet, you can modify the formula to account for this. For example: 1. Assume the `Bookings` sheet contains: - Start dates in column `A` (`A2:A10`). - End dates in column `B` (`B2:B10`). 2. Modify the formula to: ```excel =SUMPRODUCT((E9>=Bookings!$A$2:$A$10)*(E9<=Bookings!$B$2:$B$10))>0
“`
– This checks if `E9` falls within any of the start-end date ranges.
3. Apply this formula to the same range (`E9:AI9`) and test with multiple bookings.
—
This setup will dynamically highlight calendar dates based on the start and end dates in your booking sheet, ensuring a seamless and flexible solution for your requirements!