Re: [libreoffice-users] conditional formatting based on day of week

At 18:01 28/05/2021 -0400, Gary Dale wrote:
> 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?

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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy