alternating row format by week

[Possum Stu]

In column 1 of my table I have a series of dates stretching over many
months. They are sorted in chronological order, but they are often not
consecutive. They skip days, and sometimes the days will skip a week.
The table could be made much easier to read if the dates were broken up
by week.

I have seen conditional formatting formulas for alternating row colors
based on whether the row is odd or even, or in groups of 2 or 3 or
more. I have also seen conditional formatting formulas for singling out
the days in this week, last week, next week, etc. Can anyone help me
combine the two?

I want to apply a conditional formatting formula to all the rows that
applies an alternating row format based on which week the date falls.
That is, every seven days should be broken by a border, or formatted
with a recurring shading, even if the first date used on that week is a
Tuesday and even if a previous week is missing. Can anyone help?

 

[Earl Kiosterud]

Possum,

The following formula ought to work in conditional formatting:
=MOD(WEEKNUM(A1),2)=0

I get an error griping about a reference to another sheet. The formula
works in a cell. You could put it in a cell, copy it down, and use
conditional formatting. This is for where the active (white) cell of your
selection is in row 1:
=B1

 

[Earl Kiosterud]

Possum,

That post got out before I was quite finished. I meant to save, not send.

The CF formula I gave (=B1) presumes the formula (=MOD(WEEKNUM(A1),2)=0) is
in B1 and down.

 

[Possum Stu]

Thanks for the leads. Here is the answer I have so far -- it doesn't
take me all the way, but it's good enough for now.

Your answer suggested the creation of additional cells to generate keys
for the formatting. I was hoping for a self-contained conditional
format formula that only referenced the date cell itself.

The error about referencing another worksheet comes from the use of
WEEKNUM in a conditional format. WEEKNUM is a supplemental function
provided by the Analysis ToolPak, which is treated as a separate
worksheet. So WEEKNUM is out; I need a manual approach.

My research led to Chip Pearson's web site, specifically his discussion
of Week Numbers in Excel at http://cpearson.com/excel/weeknum.htm. He
lays out a method for generating ISO Week numbers that has no need for
the ToolPak.

I selected the range of rows and applied the following formula to
conditional formatting:

=MOD((1+INT(($A2-DATE(YEAR($A2+4-WEEKDAY($A2+6)),1,5)
+WEEKDAY(DATE(YEAR($A2+4-WEEKDAY($A2+6)),1,3)))/7)),2)=0

Explanation: MOD(x,y) returns the remainder after dividing x by y. All
my numbers here are already integers, so dividing x by 2 means I'll
have remainders of zero or one. For the first number, I plugged in the
ISO Week number formula from Chip Pearson's web site, substituting $A2
for the reference because that is the first cell with a date in it in
my worksheet.

This solution isn't perfect because (1) week 53 and week 1 are both odd
and thus won't alternate, and (2) the formula doesn't account for
skipped weeks in my column of dates. But I'm further along than I was
when I started. Thanks for your help.

 

[Earl Kiosterud]

Possum,

=MOD((INT((A1)/7)),2)

This works properly across year boundaries, and could be adjusted for an
offset for where weeks start, if desired. But it doesn't alternate when a
week has been skipped. That would require the formula examine an
unspecified count of prior dates to determine what to do. I suspect such a
formula would require more coffee than I have on hand. Mayhaps someone
geekier than we will tackle it.

This would be easier in a UDF, but UDF's can cause some bizarre problems
when used in conditional formatting. If you're willing to put it in a
(hidden) column, we could do it, methinks.