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

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.

On 2021-05-28 20:29, Brian Barker wrote:
> 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