Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I have a date column, A7:A, I wanted rows to be coloured every other week, A7:A may have duplicate dates but I want all the dates that lie on the same week to be one colour and alternate so it's easier to read? Week would start from Monday to Sunday.
I've tried: Conditional Formatting → Custom Formula → =WEEKDAY(A7:A, 2) > 0
If every week is represented (i.e., the dates are not skipping any weeks), then conditional formatting with the custom formula =isodd(weeknum(A7, 2)) does the job. (=iseven(weeknum(A7, 2)) works too, naturally.)
The second argument 2 is needed because your weeks are Monday-Sunday. If it's not included, weeknum uses Sunday-Saturday weeks.
If there may be some gaps, use conditional formatting with custom formula
=isodd(countunique(arrayformula(weeknum($A$7:$A7, 2))))
That is, count the number of different weeks represented in the column so far, and format if this number is odd.
As usual, the formatting is applied to the entire range such as A7:P at once; the formulas stated above will be correctly interpreted for the cells below A7, using relative references.