conditional formatting based on day of week

users
#1

It's been years since I tried to do anything with conditional formatting and now I can't seem to get it right.

I have a spreadsheet sorted in date order where I want to highlight the start of each week (i.e. Sunday) to divide up the data visually. My idea was to test if the date cell in the current row was a Sunday, so I constructed the formula as:

(WEEKDAY(ADDRESS(ROW(), "B"), 2) = 0)

where column B contains the dates. I apply that condition to a selection that excludes the column titles and set the formatting to a background highlight. Unfortunately it doesn't do anything and I can't figure out why.

Any ideas?

Thanks in advance.

#2

Yup! There are a number of faults here:

o The ADDRESS() function requires the *number* of columns and rows, not their letter designations, so you need 2 for the column, not "B".

o The ADDRESS() function requires a third parameter, indicating what sort of cell reference you need. It probably doesn't matter which you choose, but you do need to specify it. Choose 1 for absolute, producing "$B$n".

o The result of the ADDRESS() function is text, not a cell reference. You would need to apply the INDIRECT() function to the text in order to interpret it as a cell reference. But the INDIRECT() function will do the work for you, so ditch the ADDRESS() function and use
INDIRECT("$B$"&ROW())
instead.

o The WEEKDAY() function never gives 0 for Sunday. Instead it gives 1 if Type is 1 or omitted and anything else you want between 1 and 7 depending on Type, but never 0.

o The containing parentheses are unnecessary and probably confusing.

Try something like:
WEEKDAY(INDIRECT("$B$"&ROW()))=1

I trust this helps.

Brian Barker

#3

Try this:
Highlight full range of Area
In my test A1:F419
For Condition 1
Set to Formula is:
Formula to weekday($a1)=1
Weekday returns 1 for Sunday on my setup (1 to 7)
Just select ERROR as Style (for testing)

With the $a1 it will hightlight whole row of data instead
of just that column.

<users@global.libreoffice.org>
based on day of week
Date sent: Fri, 28 May 2021 18:01:38 -0400

#4

Thanks. I misread the help document on Weekday, and didn't read the ADDRESS help. I initially was using INDIRECT but it wasn't working for the reasons you mentioned.

#5

Thanks. Good solution.