This post explains how to use the ISBETWEEN function in conditional formatting in Google Sheets. As the title suggests, we’ll explore how this function simplifies writing custom rules for highlighting cells.

The formulas based on ISBETWEEN return either TRUE or FALSE (Boolean values). In conditional formatting, we use these values to determine whether a cell should be highlighted.

With ISBETWEEN, you can rewrite many of your earlier conditional formatting rules in a cleaner and more readable way.

Traditionally, if you have been using Google Sheets for a long time, most “between two values” checks (for numbers, dates, or text) would be written using comparison operators. While comparison operators still work, ISBETWEEN makes your rules easier to read and maintain.

ISBETWEEN Function in Single Column Conditional Formatting

Consider a simple dataset containing a single column of dates. Let’s highlight all the dates that fall within the first fortnight of March 2021.

Formula:

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

Explanation:

• $A2 → the first cell in the date column (excluding the header).
• DATE(2021, 3, 1) → start date.
• DATE(2021, 3, 14) → end date.

The ISBETWEEN formula checks whether the date in cell A2 falls between 01/03/2021 and 14/03/2021. If TRUE, the conditional formatting rule highlights the cell.

To highlight the second fortnight, simply update the formula to use:

=ISBETWEEN($A2, DATE(2021, 3, 15), DATE(2021, 3, 28))

Apply to Range Setup

• Under Apply to range, enter A2:A (or A2:Z if you want entire row highlighting).
• Use $A2 in the formula so that the rule only tests values in column A, even if you apply it across multiple columns.

This is a basic but practical example of the ISBETWEEN function in conditional formatting.

ISBETWEEN with Two Column Date Ranges

Now let’s look at a slightly advanced example where we have two columns:

• Column A → Start Date
• Column B → End Date

We want to highlight rows where today’s date falls within the range specified by columns A and B.

Formula:

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

Explanation:

• TODAY() → the current date (value being tested).
• $A2 → start date.
• $B2 → end date.

If today’s date lies between the values in columns A and B, the entire row is highlighted. You can replace TODAY() with any other date to test a different condition.

This demonstrates how flexible the ISBETWEEN function in conditional formatting can be when working with dynamic ranges.

You can also use ISBETWEEN to highlight rows where an actual value falls between a start and end column (similar to checking test results against their limits). For a detailed example, see Find Whether Test Results Fall within Their Limit in Google Sheets.

NOT with ISBETWEEN in Conditional Formatting

In the previous examples, TRUE values triggered highlighting. But what if you want to highlight values outside a specified range?

Example: You have time in column A and heart rate in column B. You want to highlight cases where the BPM is below 50 or above 100, assuming 50–100 BPM is the “normal” range.

Formula:

=AND(LEN($B2), NOT(ISBETWEEN($B2, 50, 100)))

Explanation:

• ISBETWEEN($B2, 50, 100) → returns TRUE if BPM is between 50 and 100.
• NOT(...) → flips the result, so values outside this range return TRUE.
• LEN($B2) → prevents highlighting blank cells.
• AND(...) → ensures both conditions are applied.

This highlights only the values that fall outside the safe range, which is useful for flagging exceptions.

Key Takeaways

• The ISBETWEEN function in conditional formatting simplifies rules and makes them easier to read than using comparison operators.
• It works for numbers, dates, and text.
• You can use it with NOT, AND, or other functions to handle more complex scenarios.
• It’s especially handy for highlighting date ranges, number ranges, and exceptions.

Resources

• How to Use ISBETWEEN with FILTER in Google Sheets
• COUNTIFS with ISBETWEEN in Google Sheets