Are you looking to level up your conditional formatting skills in Google Sheets? Well, you’re in luck! In this article, I’ll be diving into the ISBETWEEN function and how you can use it to enhance your formatting game. Get ready to make your Google Sheets stand out from the crowd!
Using the ISBETWEEN Function
The ISBETWEEN function is a powerful tool that allows you to highlight specific data based on a range of values. By returning TRUE or FALSE, this function brings a whole new level of readability to your conditional formatting rules.
In the past, you may have relied on comparison operators to achieve similar results. While those operators still work, the ISBETWEEN function offers a more reader-friendly approach. It’s time to make your formatting rules shine!
Let’s take a look at some examples to see how the ISBETWEEN function can be used in conditional formatting in Google Sheets.
ISBETWEEN Function in Single Column Conditional Formatting
Imagine you have a column of dates and you want to highlight the first fortnight in March 2021. Using the ISBETWEEN function, you can achieve this effortlessly.
Here’s an example formula to highlight the desired range:
=ISBETWEEN($A2,date(2021,3,1),date(2021,3,14))
In this formula, $A2 represents the first cell in the range, while date(2021,3,1) and date(2021,3,14) specify the start and end dates respectively. If a date falls within this range, it will be highlighted.
To apply this rule to the entire column, enter A2:A as the “Apply to range” within the conditional format rule. And don’t forget to insert the formula in the custom formula field.
Feeling adventurous? You can modify the formula to highlight a different range by changing the start and end dates. The possibilities are endless!
ISBETWEEN in Formatting Cells Based on Two Column Date Ranges
Let’s explore another example that showcases the power of the ISBETWEEN function in conditional formatting.
Suppose you have two columns – one for start dates and another for end dates. Your goal is to highlight rows based on whether today’s date falls within the date range.
Here’s the formula to achieve this:
=ISBETWEEN(today(),$A2,$B2)
By using today() as the value to test, you can dynamically highlight the rows that match the current date range. Replace today() with any other date to highlight rows accordingly.
NOT and ISBETWEEN Functions in Conditional Formatting
Now, let’s tackle a slightly different scenario. Imagine you have a column with time data and another column with heart rates in beats per minute (BPM). You want to flag cells where the BPM is either greater than 100 or less than 50.
To accomplish this, we can combine the NOT and ISBETWEEN functions in conditional formatting. Here’s the formula:
=AND(LEN($B2),NOT(ISBETWEEN($B2,50,100)))
By using the LEN function to avoid blank cells and applying the NOT function to the ISBETWEEN result, we can format the cells that fall outside the desired range.
Remember, the AND function is used here to test both conditions and ensure that they are both true before applying the formatting.
And there you have it! With the ISBETWEEN function and a little creativity, you can take your conditional formatting skills to new heights in Google Sheets.
If you want to learn more about other useful functions and tips in Google Sheets, head over to Crawlan.com. Stay tuned for more exciting content to level up your spreadsheet game. Enjoy and happy formatting!
