3

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 FormattingCustom Formula=WEEKDAY(A7:A, 2) > 0

2

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.

  • works, but it does not colour all rows A7:P1000, it only works on column A? – Spongebob Squarepants Feb 22 '16 at 20:36
  • Now it works everywhere - I added dollar signs to make column reference absolute. – user79865 Feb 22 '16 at 21:04

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.