Set conditional formatting on a column that depends on another cell's text (not value)

Question

I am making a shift calendar. I have it so my calendar dates are displayed with the day names and they are a formula so if I enter one date, the rest of the calendar changes to follow suit.

I want the columns to highlight yellow if the days are weekends. For example, if B1 has the text "Sat" or "Sun" I want the entire B column to turn yellow. But since the value is the numerical date and not the text "Sat" I don't know how to make the conditional formatting work. Do I need a script or is there a formula that could work?

Answer 1

Since you use the date value, we have a nice weekday() function we can make use of.

weekday(*yourDate*,2) will give you an integer from 1-7 counting from Monday. On the conditional formatting applied to your targetted range, we can then use the following custom formula to highlight weekends:

=weekday(B$1,2)>5

Answer 2

To add to Irin's answer, here is a formula that you can use for general situations.

=match(B$1,{"Sat","Sun"},0)

See here for documentation on match.

{} is the format to concatenate arrays. The operator ; concatenates arrays of matching length vertically, ie. stacking rows; while , concatenates horizontally, ie. stacking columns. In this case, we are just creating a single row ad hoc comprising of the list of strings you would like to match to.

{} and the concatenation operations are convenient tools. It's nevertheless better for you have a place where you store all possible strings and use that array instead of the ad hoc {"Sat","Sun"}. You can then use data validation to ensure you are inputting the right string every time.

Besides match, you can also use find and obviously a combination of exact and or.

---

For completeness, I shall also include a quick review on custom formula in Conditional Formatting.

Recall that with any custom formula in Conditional Formatting, you are always inputting a range and a formula relative to a single cell. The custom formula should always output a boolean value. The spreadsheet will automatically iterate the cell indices across your range.

Suppose your range is A1:C3 and your formula depends on B3. That means, when it comes to formatting, all cells in A1:C3 should use the value in the cell 1 column to the right and 2 row below as the input of your formula. For example, if your formula is isblank(B3). Then C3 will depend on whether D5 is blank.

On top of that, $ operator works as usual.