0

I would like to format Google Sheet ranges like this;

Setup:

Date in A1

Day of the week formula in A2 (IF(WEEKDAY(A1)=1,Settings!$B$8,...) //Settings contains the text for the day

Range1 C5:O15

If A2 = "Monday"
Cells C6,C8,C9 background should be "black"
Cells C10, C11, C12 background should be "white"
Cells C13, C14, C15 background should be "black"

If A2 = "Tuesday"
Cells C6,C8,C9, C10, C11 background should be "white"
Cells C12, C13, C14 background should be "black"
Cells C15 background should be "white"

The premise is as follows;

If the cells are white, a time slot is available, if they are black, it is not. Depending on the day, working hours are different so on Mondays one would not be able to make appointments at certain times and when the day changes those times would become available and others would not.

Graphically;

Day1 X X X O O O X X X

Day2 O O O O O X X X O

The available times never change themselves, they are constant. Day and date change. I can make this if I make separate ranges for each day in question but it is not practical. Not sure how to do it dynamically.

0

You need to apply a custom function within the Conditional Formatting Rule, and when formatting a range, the custom function needs to use an absolute cell reference.

For example: If A2 = "Monday" there are two rules:

  • Cells C6,C8,C9,C13,C14&C15 background should be "black";
  • Cells C10, C11, C12 background should be "white"

The custom formula for both rules would be: =$A$2="Monday".

Though obviously the range and background would vary for each rule: one for the "black" and one for the "white" (I used orange so that the background would stand out).

Screenshot

Similarly: If A2 = "Tuesday" there are two rules:

  • Cells C6,C8,C9,C10,C11&C15 background should be "white"
  • Cells C12, C13, C14 background should be "black"

The custom formula for both rules would be: =$A$2="Tuesday".

Though obviously the range and background would vary for each rule: one for the "black" and one for the "white" (I used blue so that background would stand out).


enter image description here


FWIW, I used a single formula in Cell A2:

  • =CHOOSE( weekday(A1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
6
  • I have tried what you suggested however it did not help me. My explanation might have not been sufficient. Please take a look at the sheet (stripped down version). In your example the cell where you wrote Monday and Tuesday, that whole layout will repeat every n of rows and to add so many conditional rules to account for every possibility is not practical. Upon further research I found that conditional formatting slows down a sheet by a lot. [link]docs.google.com/spreadsheets/d/… – SystemWorks Sep 12 '20 at 23:47
  • At this point I believe a script would be better. I tried but am getting nowhere. The file should be self evident. – SystemWorks Sep 12 '20 at 23:49
  • I have added a sheet ("Tedinoz)" to your spreadsheet. This provides the full formatting for the range C15:O15 as you specificied in your question. There are only four rules, two for each day - one for the black cells and one for ther white cells (I've used light orange to make them standout). At the very least this should enable you to clearly understand the design of the rules. – Tedinoz Sep 13 '20 at 1:39
  • I regret that your revised sheet is not helpful in understanding what you are trying to do. I can understand the increase in the number of ranges, but you have changed the number of rows as well as added checkboxes - the function and relationship of these are not obvious. I think you made a mistake in designing your question; I suggest you raise a new question (do not rely on this question for background) and also include an example of a successful outcome. BTW, if this answer has been helpful, you might consider accepting it. – Tedinoz Sep 13 '20 at 1:40
  • I checked your sheet. What you did is basically what I need. It will however repeat 31 times, one instance for each day of the month. Basically what you did, leave an empty row and then copy and paste below it. I accepted the answer as it solved the issue as it's written. I should have written it better. Each of 7 days will have different formatting rules though, Mondays will always be same but will be different to Tuesdays. Since it will be a lot of rules it might make the sheet slow, would script be better? Especially as the formatted cells do not need to change dynamically. – SystemWorks Sep 14 '20 at 2:31

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.