Go Back

Highlight dates in the next N days




So let’s say there is a need for us to highlight dates in the next N days. We can perform this task with the help of conditional formatting. We will use a formula that will include the AND function and TODAY function and it will result in highlighting the dates that occur in the next N days. This way we can easily flag dates like some deadlines may be highlight expired dates in excel or some dates relative to the current date.


Here we take an example, if we have dates in the range C10: F20, and now we want to highlight the cells that occur in the next 15 days. We will have to follow these steps:

  •  First, we will select the range, i.e. C10: F20.

Figure 1. Select the range


  • Then we will create a new Conditional Formatting rule that uses the below-mentioned formula


Figure 2. Highlight Cell Rules


Figure 3. Create Conditional Formatting Formula


  • Now save the formula. We will see that the dates occurring in the next 15 days will get highlighted.

Figure 4. Cells got highlighted


Important Notes: In step 2, The CF formulas must always be entered relevant to the “active cell” from the whole selection; in our example the cell is C10

How the CF formula works:

The function AND works by taking multiple numbers of arguments and it only returns TRUE when all of the arguments under consideration return TRUE. And the TODAY function is used to return the date today. Every date in MS Excel is depicted by a serial number, so it is easy for us to get a relative date by simply adding or subtracting a numeric digit in the TODAY function. TODAY() + 15 returns a date 15 days ahead of today’s date. So this formula will work and return true when both the functions will be true, this means that the date that is greater than today and also less than or equal to Today() + 15. With both conditions satisfied the AND function will return TRUE and the rule will hence be triggered.

Highlight cells with variable days:

We can also use variable days in our formula as we like. For example, we can use this rule for variable days like to highlight cells in excel if the date is within 7 days:

=AND(C10>TODAY(),C10<=(TODAY()+7)) // next 7 days

=AND(C10>TODAY(),C10<=(TODAY()+30)) // next 30 days

Use other cells for input:

So there is also no need to influx the dates in the CF rule, we can also make it more flexible. We can use other cells and make them variables in our rule. Let us name the cell F4 ‘days’ and now the formula will change accordingly,


The conditional formatting rule, when applied works accordingly even if you change the dates. Now let’s assume that in cell D12 the CF rule was applicable and it was highlighted. When you change the date to somewhere outside the range you mentioned, the highlight will disappear instantly. So this was all how you can highlight the dates in the next N days through Conditional Formatting in MS Excel.

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