Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Highlight dates between

We can apply conditional formatting to cells containing dates to highlight dates between two dates. Using the AND function in conditional formatting custom formula rule, each date in selected cells is evaluated against two given dates and all those cells are highlighted where dates fall between given dates. This article will step through the process.

Figure 1. How to Highlight Dates Between

Suppose we have a dataset of various tasks with their assigned and completion dates. We want to highlight those completion dates in column E that fall between 1/11/2019 and 1/25/2019, given as Date 1 and Date 2.

Figure 2. A dataset of Assigned Tasks

Steps to Highlight Dates Between

  • Select the range of cells where completion dates are listed (E2: E11)
  • Go to Home tab > Conditional Formatting > Select New Rule

Figure 3. Creating Conditional Formatting Rule

  • In New Formatting Rule window, select rule type “Use a formula to determine which cell to format”.
  • In the formula bar, enter the formula =AND($E2>=$H$2,$E2<=$I$2)

Figure 4. Creating a Custom Formula Rule

  • Now, click on the Format button, and select the Cells Formatting style like Font, Border or Fill. For our example, select Fill > Red color and press OK

Figure 5. Selecting Cells Formatting Style

  • To apply the custom formula rule and selected formatting style again press OK

Figure 6. Applying Conditional Formatting Rule

The AND formula evaluates each date in selected cells in E2: E11 against two dates given in cells H2 and I2. The formula returns TRUE where the dates in cells E2: E11 fall between the given dates and cells are conditionally highlighted with the selected formatting style.

Figure 7. The output of Conditional Formatting

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I need a formula where s36 = 1 if a10=s7 and c10=any value?
Solved by G. J. in 13 mins
Yes hello Im looking for help on conditional formatting I currently have a conditional format for D9>D32 and so on from D9:S20. I need to add an if statement that references cell D4 if the first four characters are 1358 for the rule to apply can you please assist?
Solved by Z. U. in 20 mins
Hello good afternoon, I have a problem with excel. I need a formula to compare data between 3 columns and give me the result in a fourth column
Solved by X. B. in 58 mins
I want to be able to alter the appearance (font color and fill) of a column of numbers using conditional formatting, based on being greater than or less than the numbers from another column. I am using Excel 2016. I am unable to get any formula to work. Please help!
Solved by I. J. in 59 mins
I need assistance with an excel formula. I have a 12 month sheet that has a summary page on the 13 page. the monthly totals changes so on the summary page I want to sum the difference - eg. if Jan total is 1500, feb total is 1000, mar total is 800, I want the summary page to show the current amount of 800 when you place the formula to capture all months---showing the diminishing value rather than the sum of all the pages
Solved by E. L. in 60 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc