0

I have made a calendar in Excel with each day being 2 cells wide and 9 cells tall. The first cell (top left) contains the date. An example cell might be the range from A2 to B10 with the date in A2. This pattern repeats for 7 groups across (14 columns) and an infinite number of rows down, providing an endless calendar.

I would like to apply conditional formatting to grey out an entire "day group" once the date is in the past. I know I can grey out the date (top left cell within the group) by applying A2<TODAY() but I want to apply the formatting to that cell as well as the cells to the right and below (the whole range of 9x2)...and then apply the rule to my entire calendar. Is there a way to provide a grouping definition that the condition will apply against?

enter image description here

2

With the assumption, the days are actual dates custom formatted as d you can do the following:

  • In my case, below, select range A1:N37
  • New conditional formatting rule:

    =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-MOD(ROW()-2,9),-MOD(COLUMN()+1,2))<TODAY()
    
  • Apply a formatting and confirm. Result as below.

enter image description here

Beware, this is volatile (since all custom formatting is volatile, as per @teylyn her comment), but since it looks like your worksheet won't be heavy on calculation anyway, you should be fine.

You asked me, how does this work? We will have to break it down in parts:

  • The OFFSET function will return the value from a cell that is N rows and N columns offset from a referenced cell. This is our main function here.
  • OFFSET needs a reference address from which you want to actually offset from. I used ADDRESS function, which should return an address.
  • ADDRESS needs at least a row and column, which we gave the formula with ROW() and COLUMN(), which both look at the row and column of it's current cell (makes sense in conditional formatting since the formula will apply to all cells). The rest of the parameters are optional and we can leave them out.
  • We use INDIRECT() to actually return the valid address.
  • The second important piece of the OFFSET function is to feed it a rownumber to offset with. I used MOD() which will return, in this case, an integer of the remainder of two numbers after division. e.g cell C10 will return -MOD(ROW()-2,9) > -MOD(10-2,9) > -MOD(8,9) > -8.
  • The same trick we do with the column parameter, -MOD(COLUMN()+1,2) > -MOD(3+1,2) > -MOD(4,2) > -0. Notice how in both parameters we used the - to create a negative offset?
  • The above will create an offset for each cell redirecting to it's appropriate corresponding date. Which we simply evaluate against TODAY(). Anything in the past will fit our rule and have conditional formatting.

Hope that makes sense :)

| improve this answer | |
  • All conditional formatting is volatile. – teylyn Jul 16 '19 at 22:02
  • @teylyn very true, for some reason my alarm bells go off seeing and using Indirect and Offset ;) – JvdV Jul 16 '19 at 22:10
  • this works perfectly. thanks! can you explain the steps so I can learn how this works for the future? – techtheatre Jul 16 '19 at 22:22
  • @techtheatre, gald it worked for you. I have updated the answer with a (hopefully) understandable breakdown of the formula for future reference. – JvdV Jul 17 '19 at 7:05

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.