• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting - Only format days of the month, not previous or next

Yodelayheewho

New Member
Attached is a calendar and I have conditional formatting to format days for events and holidays. However, it is also formatting the previous and future dates (grayed out) within each month. For example, May 1, 2019 is on a Wednesday. On a calendar, we see April 28, 29, 30 filling in the first half of the first week. In this example, I would NOT want the conditional formatting to effect April 28, 29, 30; only May 1 thru May 31.

May 2019
S M T W T F S
28 29 30 1 2 3 4


Thank you in advance for you consideration!
 

Attachments

Hui

Excel Ninja
Staff member
Using June as an Example
June is J16: P21

Select that edit the Orange CF
Use this modified formula
=AND(MONTH($J$14)=MONTH(J16),OR(B7=$R$37,B7=$R$39,B7=$R$40,B7=$R$41,B7=$R$42,B7=$R$43,B7=$R$44,B7=$R$45,B7=$R$46))
etc

ie: Use the Month of the Month and Month of the Date as an extra check

You will need to adjust the CF's for each range individually
 

Peter Bartholomew

Well-Known Member
@Yodelayheewho [Who?]
Firstly, congratulations on a stylish piece of work!

My first step towards providing a possible solution to your problem was to create a formula that would return the expected month from any cell in the calendar. I called the formula 'CalendarMonth' and defined it to refer to
= 1 + 4*QUOTIENT(ROW()-5, 9) + QUOTIENT(COLUMN()-1, 8 )
That allowed me to replace the hard-wired month numbers in your array formula for the days of each month.
=DaysAndWeeks+DATE(CalendarYear,CalendarMonth,1)-WEEKDAY(DATE(CalendarYear,CalendarMonth,1),(WeekStart="Monday")+1)+1
and make the month names more transparent
= DATE( CalendarYear, CalendarMonth, 1)

I don't enjoy developing formulas within the conditional formatting box so I defined Boolean Names 'Holiday?' and 'Event?' to refer to
=OR(CDay=HolidayList) * (MONTH(CDay)=CalendarMonth) and
=OR(CDay=EventList) * (MONTH(CDay)=CalendarMonth)
where 'CDay' is a relative reference to the current day of the calendar.

The conditional formats refer to these Boolean values.
 

Attachments

  • Like
Reactions: Hui

Yodelayheewho

New Member
Peter,
I apologize for not responding quicker. Thank you for your help because the Calendar works perfectly. You fixed the issue I was having. I can't take any credit for the 'stylist' appearance of the calendar. I found it online several years ago;)

I am pretty clueless on Boolean values and use A1 Notation, not R1C1. I was able to switch your spreadsheet to A1 notation without any issues.

Name = Day; Refers to: ='2019 Calendar View'!RC (see attached image). What does this really do? I can't find how this impacts the calendar. I assume the 'RC' means Row and Column, but there are no numbers just 'R' and 'C'?

All of the functions you did are completely foreign to me and I wish I understood how they work. Perhaps I need to learn Boolean and how I can expand my knowledge of how to use Named Ranges.

Thanks again!!!

60414
 

Peter Bartholomew

Well-Known Member
As you see from the formulas, I tend to avoid both A1 and R1C1 notations for referring to data by its location and instead use names to identify it. The name CDay (think of that as either the calendar day or current day) simply references the active cell. The missing numbers with R and C could be considered to be 0, so '2019 Calendar View'!R is the entire row through the active cell and '2019 Calendar View'!C is the entire column.
Name = CDay; Refers to: ='2019 Calendar View'!RC
is the single cell with 0 row and column offsets.

The function QUOTIENT is simply integer division; that is divide the first parameter by the second and discard any remainder. Starting at the January month name as (0,0) the CalendarMonth
= 1 + 4*QUOTIENT(ROW()-5, 9) + QUOTIENT(COLUMN()-1, 8 )

will increment every 8th column and increment by 4 every 9th row. Using CalendarMonth means that the month is no longer hard-wired into each data block. It also allows me to test the current day to determine whether it is a day in the same month as the block in which it appears as in the term
(MONTH(CDay)=CalendarMonth)
The other part of the expression for identifying holidays
OR(CDay=HolidayList)
generates an array with TRUE for a holiday that coincides with the current day and FALSE otherwise. The OR returns TRUE if any element of the array is true. [Note: AND would only return TRUE only if every element of the array were true]

I have also done some tidying up. I couldn't make out why the events used color whereas the holidays were in black so I reversed the name and the dates. Also, the fact that the table uses merged cells made the EventList and HolidayList pick up 2D ranges whereas only the first column of each is needed.
 

Attachments

Yodelayheewho

New Member
Brilliant! Thanks for fixing the holiday names and holiday dates to coincide with the Events. I surprise myself, because I like continuity. You've been a great help. Thanks again!!
 
Top