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.