In the event that you need to highlight dates between two dates using conditional formatting, you can utilize a straightforward formula that uses both the “AND” function and “DATE” functions together.
Let’s take for instance, on the off chance that you have dates entered into a spreadsheet in the range C4:H11, and you are required to highlight the cells that are filled in with dates lesser than December 2, 2017, and higher than September 2, 2017. Then you should highlight the range (the rows and columns where the dates are entered) and then make another CF rule while making use of this formula:
Figure 1: Highlight dates between 09/02/2017 and 12/02/2017
= AND (C4 >= DATE (2017,9,2),C4 <= DATE (2017,12,2))
Note: it’s of great importance to take note that the CF formulas must be filled in respect to the “active cell” within your selection, in other words, and in this case “C4.”
You will see immediately that the dates between 09/02/2017 and 12/02/2017 will be highlighted once the CF rule is saved. Note that we will be including the start and end dates by making use of the greater than or equal to sign (>=) and the less than or equal to (<=) symbol.
How this Formula Functions
The “AND” function in the CF formula function by taking multiple arguments and giving a “TRUE” result just only when all the arguments return as “TRUE.” The “DATE” function in the CF formula, on the other hand, creates an appropriate Excel date with given day, month and year values. Also, since the reference to C4 as used in this case is entirely relative, it will update every cell in the range as the rule is connected to cells within the range. Which means any dates that are both lesser than 12/2/2017 and greater than 9/2/2017 will be indicated or highlighted.
Utilize Other Cells for Input
You don’t have to undergo the stress of hard-coding the dates within the cell range into the rule. You can make use of the other cells like variables in the formula to make a more adaptable rule. Let’s take, for instance; you choose to name cell F2 as the “start” while you name cell H2 as the “end,” at that point you can rewrite the formula in this manner:
= AND (C4 >= start, C4 <= end)
You should be aware that the conditional formatting (CF) principle will react in a flash the moment you change any of the dates. But by making use of the other cells for input, and by naming them as named ranges, you, in turn, make the conditional formatting (CF) interactive which in turn will make the equation less demanding and more straightforward to read.