LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > General
User Name
Password
General This forum is for non-technical general discussion which can include both Linux and non-Linux topics. Have fun!

Notices


Reply
  Search this Thread
Old 11-06-2020, 06:41 AM   #1
czezz
Member
 
Registered: Nov 2004
Distribution: Slackware/Solaris
Posts: 850

Rep: Reputation: 40
Libre Calc / Excel - conditional formatting: WEEKDAY()


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
Attached Thumbnails
Click image for larger version

Name:	Screenshot_2020-11-06_12-27-14.png
Views:	18
Size:	12.1 KB
ID:	34502  

Last edited by czezz; 11-06-2020 at 06:44 AM.
 
Old 11-06-2020, 08:11 AM   #2
boughtonp
Member
 
Registered: Feb 2007
Location: UK
Distribution: Debian
Posts: 781

Rep: Reputation: 560Reputation: 560Reputation: 560Reputation: 560Reputation: 560Reputation: 560
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
 
Old 11-06-2020, 10:07 AM   #3
czezz
Member
 
Registered: Nov 2004
Distribution: Slackware/Solaris
Posts: 850

Original Poster
Rep: Reputation: 40
Thanks!
That worked
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Libre Office conditional formatting error when saving in xlsx format in 14.1 vdemuth Slackware 2 11-10-2013 03:40 AM
[SOLVED] Cronjob to run every other weekday linuxguy7820 Linux - Newbie 2 10-27-2011 02:38 PM
OpenOffice.Org =IF(WEEKDAY(DATE($O$2;B$2;$A4)<6;"dailyx")) Marshalle Linux - General 0 07-17-2005 09:45 PM
I want to find out the last weekday with shellscripting Arndt Programming 6 03-31-2005 08:30 AM
OO.org calc not multiplying my excel file np complete Linux - Software 0 10-15-2004 11:14 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > General

All times are GMT -5. The time now is 07:47 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration