Calendar formulas and conditional formatting

**enelson84** · 03-15-2024, 11:48 AM

Good Morning All,

I have created a calendar that has a full year as well as each month. I believe I have everything setup correctly so each year when I need to update the calendar I just have to change the year and the list of blackout dates and birthdays. My question is when I'm in the month view if a blackout date or birthday falls in the previous or next month either a few days before or few days after it is still showing. I don't want it to show unless its part of that month.

I have attached my file for assistance. The first occurrence is in May where Joe Apple's Birthday is June 6th so it is showing in May as it is part of that monthly view but I have the conditional formatting turn the date if its not associated with that month to White with White font.

Annual Calendar.xlsx

**JohnTopley** · 03-15-2024, 12:22 PM

As an example using formula (in F26 in "May")

Formula:

=IFERROR(FILTER(Birthdays!$G$2:$G$19,(Birthdays!$D$2:$D$19=F24)*(MONTH(F24)=MONTH($J$1&0))),"")

You will need apply (in some way) the RED part of the formula to CF if you going down that route: BUT CF can have performance impacts.

**DJunqueira** · 03-15-2024, 01:26 PM

If you are using SEQUENCE() function in your workbook it means that at least you are using Excel 2021, please update your profile for us to give you the best solution.

My solution for you has been applied to every last line of your days calendar (Rows 24).

Formula:

=LET(ld,Year!B14,IF(MONTH(ld)=MONTH(B12),SEQUENCE(,EOMONTH(ld,0)-ld+1,ld)," "))

Rows 25

Formula:

=IFERROR(IF(B24=" ","",FILTER(TBlack[[Decription]:[Decription]],TBlack[[Black out dates]:[Black out dates]]=B24)),"")

Rows 26

Formula:

=IFERROR(FILTER(TBirthday[[Date]:[Date]],TBirthday[[Birthday this Year]:[Birthday this Year]]=B24),"")

And as you can see I transformed your tables into named tables, this means your formulas are easier to read and you don't need to adjust to the increase of new data.