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 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.

Are you still looking for help with Conditional Formatting? View our comprehensive round-up of Conditional Formatting tutorials here.

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
can you teach me the steps to create a nested if function, and to nest an AND function inside of an IF function?
Solved by A. Q. in 22 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
how to make a format that if multiple cells have number contents its value is 1 in the last cell
Solved by C. C. in 28 mins
I have a spreadsheet where we track deadlines for questionnaire completions. This formula is currently set up like this: =IF(AND(ISBLANK(L2),ISBLANK(S2)),IF(TODAY()>=(K2),"Closed",IF(TODAY()<(J2),IF(TODAY()<(F2),"Not open","On-time"),"Late")),"Complete") I would like to add a condition so that if today is two weeks away from the deadline or closer, it shows up as "nearing deadline"
Solved by S. U. in 24 mins
IF(AVERAGE(C6+E6+G6)>=5000,TRUE,FALSE) I am using AND function and it is not giving me the correct answer
Solved by G. J. in 24 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