3

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?

4

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
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.