Monthly Calendar Conditional Formatting

[HatedLove6]

Hello, I’m very new to Calc, and don’t regularly use it to its fullest advantage, so I’m not fully familiar with formulas and other features. Please be patient with me if it’s complicated.

I’m trying to make a monthly calendar, and followed the steps to this website.

What I want to know is how to gray out the previous and next month. The poster of the link said it had to do with conditional formatting?

I tried to apply the formatting on the top row so that if the cell that is greater than 7, it the number would be gray, but it just kept graying out all the numbers.

I downloaded the template, and looked up their conditional formatting, but when I tried to emulate it to the entire calendar, it all turned gray.

Condition 1
Cell value is less than $Settings.$C$2

Condition 2
Cell value is greater than $Settings.$D$2

I am at a loss. I don’t know how to look into the steps he took with only the results.

[eeigor]

@HatedLove6

…but when I tried to emulate it to the entire calendar, it all turned gray.

What does it mean “to the entire calendar”? The calendar displays the specified month of the current year.
Template is readey to use.
https://calendartricks.com/wp-content/uploads/2020/LibreOffice-calendar-template-A4.ods

Settins.C2:
Used:
=EDATE(C2;1)-1
Better to use (it’s the same):
=EOMONTH(C2;0)

[HatedLove6]

“. . . it all turned gray” pertains that all of the numbers on the calendar turned gray instead of just the previous and next month on the calendar page.

I understand the template is ready to use, I would just like to understand how this person did it, so I can do it myself.

Settins.C2:
Used:
=EDATE(C2;1)-1
Better to use (it’s the same):
=EOMONTH(C2;0)

I don’t understand what that formula is for. Is it to get the numbers on the calendar? or is this somehow helping to gray out the previous and next month’s numbers?

[eeigor]

@HatedLove6

it all turned gray

What “all”?
I’ll try to guess. Do you want to display all months at the same time (whole year), not one at a time as in the example?

@HatedLove6

I don’t understand what that formula is for.

=EOMONTH(C2;0) returns the last day of the given month (EOM… stands for ‘end of month’).
=EDATE(C2;1)-1 determines the same day (here the first) of the next month and subtracts one day to get the same last day of the current month.

---

Let's analyze the 1st cell formula into its component parts.

1. =MATCH(A1;$Settings.A1:$Settings.A12;0) returns the month number;
2. =DATE(G1;MATCH(A1;$Settings.A1:$Settings.A12;0);1) returns the 1st month date;
3. =WEEKDAY(DATE(G1;MATCH(A1;$Settings.A1:$Settings.A12;0);1);2) returns the day of the week or number of days by which the 1st day of the month is shifted to the right. NOTE: For Type=2, the weekdays are counted starting from Monday=1;
4. Subtract the offset from the first day of the month and add 1 to get the date on Monday. It will be some day of the previous month, if the offset is greater than 0. All other cells are calculated by adding 1 day to the previous date.

Everything seems to be clear. A good example with formulas.

Conditional Format:
Range (cells with formulas): A3:G3;A5:G5;A7:G7;A9:G9;A11:G11;A13:G13
Condition 1: cell is less than $Settings.$C$2
Condition 2: cell is greater than $Settings.$D$2
If conditions are true, the style ‘Faded’ is applied.
Cells outside the range of days of the selected month become faded.

An interesting example. Here I figured it out myself.
And what is not clear to you? If this is a solution to the question, please check it (upvote).

Edit:
The good news is that the months and days of the week can be translated into your own language: this will not affect the calendar in any way.

[HatedLove6]

Thank you.