This post is about the use of a particular function in highlighting/formatting in Google Sheets. Yep! As the title suggests, it’s about how to use the ISBETWEEN function in conditional formatting.

The formulas based on the above function return TRUE or FALSE (Boolean values). We are making use of both of these Boolean values in highlighting.

If we want, we can rewrite lots of our earlier conditional format rules using this new function.

As I have mentioned, it’s a new function. So if you are using Google Sheets for a long, most of the is_between two values (numbers, dates, alphabets) would be based on comparison operators.

I am not suggesting that the comparison operators are outdated. They will continue to work.

But you can make the rules more reader-friendly with the help of this new function.

Here are a few examples of the use of ISBETWEEN function in conditional formatting in Google Sheets.

ISBETWEEN Function in Single Column Conditional Formatting

I have the following set of data (a column contain dates) for highlighting. I have kept it very basic for your understanding. So you can’t find any other values in other columns.

I have used an ISBETWEEN formula to highlight the first fortnight, i.e. 01/03/2021 to 14/03/2021, in March 2021 in this date range. It’s as follows.

=ISBETWEEN($A2,date(2021,3,1),date(2021,3,14))

In this, for the sake of making you understand the formula;

$A2 – the first cell in the range ignoring the field label.

date(2021,3,1) – start date

date(2021,3,14) – end date

The formula tests whether the Date in cell A2 is between (ISBETWEEN) 01/03/2021 and 14/03/2021. If TRUE, it highlights A2.

Additional Notes

This image may speak a lot about applying the format rule.

Inside the conditional format rule, please enter A2:A (or A2:Z for entire row highlighting) under “Apply to range”. It is for the formula to apply to all the cells in cell range A2:A (or A2:Z).

Since we have used $A2 (absolute column and relative row reference), the formula will only test the values in A2:A. So selecting A2:Z (apply to range) won’t have any effect on the formula output.

Related: Relative Reference in Conditional Formatting in Google Sheets.

As you can see on the image, the formula should enter under the custom formula field inside the format rules.

Change the start date to 15, i.e., date(2021,3,15), and end date to 28, i.e., date(2021,3,28), in the formula to highlight the second-fortnight range instead of the first-fortnight.

The above is my first example of the ISBETWEEN function in single-column conditional formatting in Google Sheets.

What about using ISBETWEEN in a number column in conditional formatting?

I have already posted the same under the “Conclusion” part of the function ISBETWEEN. Please pick the function from the Function Guide to see the example.

Also, there is one example at the end of this post. But in that example, I have used NOT with ISBETWEEN.

ISBETWEEN in Formatting Cells Based on Two Column Date Ranges

Here is another example of the use of the ISBETWEEN function in Google Sheets conditional formatting.

About the Data:

Let’s start with two columns of data that contain a start date column (column A) and an end date column (column B).

Since column B contains end dates, the row-wise dates in that column will always be higher than the dates in column A.

Regarding the Formula:

At the time of writing the formula, today’s date is 31/03/2021. I want to highlight the row(s) that contains the date range in which today’s date falls.

Since 31/03/2021, which is today’s date, falls between 27/03/2021 and 05/04/2021, the following formula highlights row # 7.

=ISBETWEEN(today(),$A2,$B2)

today() – value to test

$A2 – start date

$B2 – end date

This way, we can use today in the date range in conditional formatting in Google Sheets.

Replace today() with any date to highlight the row or rows accordingly.

NOT and ISBETWEEN Functions in Conditional Formatting

In all the above examples, I have used the TRUE part for highlighting.

I mean, the said function returns TRUE when the lower_value (start-date) and the upper_value (end-date) limits meet. The format rule highlights rows that return TRUE.

Here is a different scenario.

Here cell range A2:A9 contains time and B2:B7 contains heart rate in BPM (beats per minute).

We want to flag the cells if the BPM is >100 or <50 as the normal level is considered as 50-100 BPM when not working out, sick, stressed, or asleep.

Disclaimer:- 50-100 BPM may or may not be the correct level. It may be depending on other factors. I have used that range only for example purposes.

In the above scenario, we require only one rule. Here is that Not and ISBETWEEN function use in Conditional Formatting in Google Sheets.

=and(len($B2),not(ISBETWEEN($B2,50,100)))

We want to highlight the cells that do not match the lower value (50) and upper value (100) range in ISBETWEEN. That means we want to format the FALSE output cells.

Using NOT with ISBETWEEN, we can convert FALSE to TRUE and vice versa.

The LEN function helps to avoid blank cells.

Since we want to test two conditions (LEN and ISBETWEEN), the AND used.

That’s all. Thanks for the stay. Enjoy!