Conditional Formatting: Highlight Occurrences between specified date range on a calendar

Hello everyone,

I am helping my wife build a worksheet that tracks her employees' absence and tardy occurrences. There are rules that assign points for different types of absences, and these points aggregate within the year to varying levels of discipline - then "roll off" after one year.

She has asked to viewthe entries by both current year and "rolling year" (i.e., current month and previous 11 months). See attached calendar views.

The conditional formatting formula works fine for the current year view. It is not working for the "rolling year" view.

Here is the formula I am using: =COUNTIFS(EmployeeLeaveTracker,valSelEmployee,BeginLeaveDate,"<="&C6,EndLeaveDate,">="&C6,TypeOfLeave,'Leave Types'!$B$7)>0

I'm sure the search method is affected by the way in which the "rolling year" dates are populated in the calendar view, but I can't seem to find a solution. Any ideas?

Calendar View.png

Rolling Year View.png

Without seeing the whole thing it would be too much guesswork to try and figure out what is not working and why.

Please attach a copy of the file rather than screen captures, before doing so please replace any confidential information with fictional. Cartoon characters for employee names, that sort of thing. That way we can see exactly what you have to work with.

File attached.

The "Count of Occurrences" section works as required. It is the conditional formatting of the "rolling calendar view" section in which I have the issue. Both "Jane Doe" and "Mike Doe" have the correct data highlighted in the "calendar view." In the "rolling year" view, Jane populates nothing, whereas Mike has erroneous data.

Thanks for the help.

Only had a quick glance at it so far, first observation, the calendar doesn't appear to be populating correctly for rolling year (every month has 31 days).

Looks like everything is defaulting to current month regardless of the month name in column B.

I have a short formula that should fill the calendar correctly, will look it up later when I get home.

Have a look at this, see if it works as you need it to.

I've changed the formulas in the calendar so that it now fills correctly, I don't think that the changes I've made have caused anything else to stop working.
B6:B17 now contain dates formatted as month names, instead of just month names as text. Real dates are much easier to work with as you will see from the formulas filling the days in the rest of the calendar.

One thing that I would suggest, which I haven't done, is to disable the 'Calendar Year Selection' in D3 if Rolling year is selected, or perhaps blank it out with conditional formatting. As rolling year is tied to the current date rather than the year selected, it could lead to confusion.

You are awesome, thank you so much!