Google Sheets – Alternate Background Colors and Conditional Formatting Based on Date

conditional formattinggoogle sheets

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

Best Answer

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.

Related Topic