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