## Highlight dates in a date range

**Question**: How do I highlight dates that meet criteria using conditional formatting?

**Table of contents**

### Highlight values in a column

### How to apply the conditional formatting formula

- Select the range (B3:B11)
- Press with left mouse button on "Home" tab on the ribbon
- Press with left mouse button on "Conditional formatting"
- Press with left mouse button on "New rule..."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Press with left mouse button on "Format values where this formula is true" window.
- Type
=(B3<=$E$3)*(B3>=$E$2)
- Press with left mouse button on Format button
- Press with left mouse button on "Fill" tab
- Select a color
- Press with left mouse button on OK!
- Press with left mouse button on OK!

### Explaining CF formula

#### Step 1 - Check if current cell date is smaller than or equal to end date

B3<=$E$2

becomes

39823<=39833

and returns TRUE

#### Step 2 - Check if current cell date is larger than or equal to start date

B3>=$E$2

becomes

39823>=39823

and returns TRUE.

#### Step 3 - Multiply boolean values

Both boolean values must be TRUE in order to highlight the cell.

Boolean |
Boolean |
Multiply |
Add |

FALSE | FALSE | 0 (zero) | 0 (zero) |

FALSE | TRUE | 0 (zero) | 1 |

TRUE | TRUE | 1 | 2 |

The parentheses make sure that the order of calculation is correct.

(B3<=$E$3)*(B3>=$E$2)

becomes

TRUE*TRUE

and returns 1 which is equivalent to TRUE. Cell B3 is highlighted.

### Highlight values in a row

Conditional formatting formula in cell range A5:E5:

This formula is the same as the above (not the cell references though), the difference is that it is applied to a horizontal cell range.

### Highlight records

Conditional formatting formula in cell range A6:C15:

The cell references are "locked" to column A, that is why there is a dollar sign in front of $A6. That makes the entire row highlighted if the date cell is in the date range.

### Sort values

You can quickly sort highlighted records to the top of the list.

- Select cell range A6:C15
- Press with right mouse button on on cell range
- Press with left mouse button on "sort"
- Press with left mouse button on "Put selected cell color on top"

All highlighted records are on top.

**Get excel *.xls fil****e**

highlight-dates-in-range-using-conditional-formatting2.xls

(Excel 97-2003 Workbook *.xls)

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]

This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]

### 15 Responses to “Highlight dates in a date range”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I'd like to do this for a row instead of a column, but I'm stuck. (I replace 'A1' with '1:01'.) Can this be done?

Kate,

I have have added more content, I hope it will answer your question.

i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart)

i have a schedule table with event in 1st column, start date in 2nd column, end date in 3rd column, and a 2-criteria condition for the event in the 4th column.

how do i condition format the cell row beneath the calendar row so that they are within the start/end date and will be color coded brown or blue based on the 2-criteria? I already have a conditional formatting done for weekends and holidays for the calendar row itself, so hence the new row beneath.

bryan,

Can you provide example data and desired outcome?

Oscar,

Thank you for the reply. Further info provided:

I have a schedule table with 4 columns.

1. Column D has the event name

2. Column E has the Start date

3. Column F has the End date

4. Column G has a drop-down choice of "home" or "out of area"

I have a calendar going from I1:IV1. I have it setup for highlighting federal holidays and weekends for conditional formatting.

I have a row setup beneath the calendar from I3:IV3 that I'd like to conditionally format to reflect based on the start-end date ranges in the schedule table with color coding for "home" or "out of area".

Thank you.

bryan,

Read this: Highlight events overlapping federal holidays

[...] Bryan asks: [...]

Hi

Regarding your section on 'HIGHLIGHT RECORDS', how do I Conditional Format to highlight dates in range that are recognised, but then automatically highlight all cells in the row?

IE: Using your HIGHLIGHT RECORDS example:

Conditional Format (as per your formulae provided) to recognises dates between 10-01-2009 & 20-01-2009

Then automatically highlight COUNTRY & COMPANY once dates recognised by Conditional Formatting.

John-Paul

I don´t understand.

The "Highlight records" example highlights all cells (also country and company) in a row?

Can you explain in greater detail?

Hi,

I am making a Schedule of Project in Excel, Where i am taking 2 column as Start date and End date. While I need that whenever i enters the start date and end date , according to that rows color changes simultaneously..... While after End date column, Next all columns belongs to week wise column. so every cell color changes accordingly with respect to weekwise.

Please let me know how to set a format like this....

Thru which color changes automatically, whenever i enters dates.

Avin

Avin,

I am not following, can you please take a screenshot of your sheet and the desired outcome. Upload to postimage.org. Then add the picture link to your comment.

I've tried the above method but only two of my dates actually got highlighted. Is there and issue in Excel 2010?

I have dates in two particular cells and I need a row of dates to highlight a particular color if the dates fall on or between the particular dates. I think the formula for rows above should work but it is not.

Jen

Jen,

I've tried the above method but only two of my dates actually got highlighted. Is there and issue in Excel 2010?No, it works in excel 2010.

Is there an issue on the attached file? The attached file works here (excel 2010).

Can you provide your Conditional Formatting formula?

Did you check the absolute and relative cell refs in your CF formula?

Here is a pdf of what I'm talking aobut. I've applied the conditional formatting, but the cells that are white (Y7 to AM7) *should* be green.

https://s26.postimg.org/p77hmqeuh/Page_1_Conditional_Formatting_Sample.jpg

Jen,

your CF formula should be:

=(Y7>=$I$7)*(Y7<=$J$7) not =(Y7<=$I$7)*(Y7>=$J$7)