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

1 Answer 1


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? Feb 22, 2016 at 20:36
  • Now it works everywhere - I added dollar signs to make column reference absolute.
    – user79865
    Feb 22, 2016 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.