Libre Calc / Excel

czezz · 11-06-2020, 05:41 AM

Hi,
As you can see in the attachment I created calendar in row 2.
With conditional formatting, I have highlighted all weekends with orange color :

Code:

=OR(WEEKDAY(D2)=1;WEEKDAY(D2)=7)

I would like to add now another condition:
whenever there is =OR(WEEKDAY(D2)=1;WEEKDAY(D2)=7) - it stands for: Saturada and Sunday - highlight whole column (or at least 10 rows of that specific column).
You can better see an example on columns: I,J in attached screenshot.
Does anyone know how to do that?

What I have came up for now, is the following condition but this is just static and does not refer to WEEKDAY()

Code:

=MOD(COLUMN();7)=1

boughtonp · 11-06-2020, 07:11 AM

To get the value of the a named cell, you can use INDIRECT - it accepts either A1 syntax, or R#C# syntax

The first is easiest, you can obtain it with ADDRESS(row,col).

So putting those together you can obtain the weekday of the date in the second row like so:

Code:

=WEEKDAY(INDIRECT(ADDRESS(2,COLUMN())))

And to match both 1 and 7, your mod 7 should be less than or equals, i.e: MOD(value;7) <= 1

So the final condition would look like:

Code:

=MOD(WEEKDAY(INDIRECT(ADDRESS(2,COLUMN()))),7) <= 1

czezz · 11-06-2020, 09:07 AM

Thanks!
That worked