Go Back

Highlight dates in the next N days

Conditional Formatting is a feature in Excel that allows us to change the format of cells based on a set of rules or conditions.  This step by step tutorial will assist all levels of Excel users in applying conditional formatting to highlight dates falling in the next N days.  

Figure 1.  Final result: Highlight dates in the next N days

Working formula: =AND(B5>TODAY(),B5<=(TODAY()+$E$2))

Syntax of AND Function

AND function evaluates all logical tests and returns TRUE if all arguments are TRUE; FALSE if one or more arguments is FALSE

=AND(logical1, [logical2], ...)

  • logical1–  the first condition that we want to test
  • only logical1 is required; succeeding logical conditions are optional  

Syntax of TODAY Function

TODAY function returns the current date

=TODAY()

  • TODAY function does not have any arguments
  • It can be used in combination with other functions and mathematical operations to obtain the desired results

Setting up the Data

Our data consists of four columns from column B to E, with each column containing five dates.  In cell F2, we enter the number “30” which serves as our criteria.  We want to use conditional formatting to highlight dates that fall in the next 30 days from today.

Figure 2.  Sample data: Highlight dates in the next 30 days

Supposing the date today is January 29, 2019, then the date 30 days from today would be February 28, 2019.  Hence, we want to highlight the dates in between January 29, 2019 and February 28, 2019.

Highlight dates in the next 30 days

We want to highlight the cells in the range B4:F9 containing the dates that fall in the next 30 days from today.  We can do this through conditional formatting and the functions AND and TODAY by following these steps:

Step 1. Select the cells we want to highlight.  In this case, select cells B5:E9.

Figure 3.  Selection of the data range for conditional formatting

Step 2. Click the Home tab, then the Conditional Formatting Menu and select “New Rule”.

The New Formatting Rule dialog box will pop up.  

Figure 4.  Creation of a new rule in conditional formatting

Figure 5.  New Formatting Rule preview

Step 3. Select the Rule Type “Use a formula to determine which cells to format” and enter this formula in the dialog box :

=AND(B5>TODAY(),B5<=(TODAY()+$E$2))

The dollar sign “$” fixes cell E2 which serves as our criteria, to enable our formula to work properly across all cells in B5:E9.  

Figure 6.  Entering the formula as a condition or formatting rule

Our AND formula contains two criteria.  First, it evaluates if the date in B5 is greater than the date today.  Next, it evaluates if the date in B5 is less than or equal to the date 30 days from today, as given by the formula TODAY()+30.  

Excel stores dates as serial numbers, making it possible to perform calculations involving dates.  In this example, we refer to the date today as January 29, 2019, while the date 30 days from now would be February 28, 2019.  

Hence, for every date in the range B5:E9 that falls between January 29, 2019 and February 28, 2019, the format will be changed.  

To change the format, let us proceed to the next step.  

Step 4. Click “Format” and then decide on the new format to apply to the cells in B5:E9.  We can change the font, borders or fill the cells with different colors.

Example

Select “Fill” and choose Orange, Accent 6, Lighter 60% and click OK.  

Figure 7.  Selection of the format to use

Figure 8.  Completion of the new formatting rule with formula and selected format

This rule highlights the cells that fall in the next 30 days from today.  As a result, cells B7, B8, C6, C8, C9, D7, D8 and E7 are highlighted as shown below.  

Figure 9.  Output: Highlight dates in the next 30 days

Using conditional formatting with the AND and TODAY functions, we are able to highlight dates that fall in the next 30 days.  With our formula, we can easily highlight our desired dates by simply changing the value of F2.

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:
Here are some problems that our users have asked and received explanations on

I need a formula to highlight dates 3 days before the TODAYS date
Solved by O. H. in 14 mins
I have 3 rows Name, SSN, Date. I have dates from today to Dec 31 I need lets say 30 days highlight red, 60 days highlight yellow and 90 days highlighted green. How would I be able to code it so when the dates change from 90 days put to 60 days out and change to there respected color?
Solved by K. S. in 12 mins
I need help with a formula to auto generate a due date in column A, Based on dates either in column L or N or P which all hold 1st customer contact, 2nd contact & 3rd contact dates and looking to auto generate the next contact date 7 days after our last customer interaction
Solved by E. H. in 19 mins

Leave a Comment

avatar