Counting "YES" values only within a moving 366 day window

Hi there,

To explain my problem, I am putting together something as a side project for our company to help track occasions of bad attendance.

Currently I have something set up that simply calculates the amount of "YES" values entered for that line for each employee, that I have set to conditionally format if it reaches a certain value or higher.

Issue is due to the company rules, any occasions that happened one year and a day ago do not count in these totals, so I have to set my sheet to look up only the "YES" values within a moving 366 day window.

I have two seperate sheets, one for lates, and one for no shows - both populate their totals on the "master" sheet currently and are virtually identical, using Drop Downs for people to choose YES/NO entries for simplicity.

One is called "Lates" and the other is called "No Shows", how can I create a formula that only looks for "YES" specifically within a 365 day moving calendar period?

My dates range from I3 to APM3 - covering a 3 year period currently which is all the project requires for now (1st Octover 2014 till 1st October 2017) and the results populate right below these directly and these "YES" results are then tabulated using a simple =COUNTIFS(Lates!I4:APM4,B3) for example where B3 is my "YES" result in the drop down list I have, with an idential =COUNTIFS('No Show'!I4:APM4,B3) for the other sheet.

Currently these results are then referenced in the master sheets "# Of Occasions" for each, with any result of 5 or higher causing the conditional formatting to highlight the cell and make it yellow to get someones attention.

Question is, how do I have it only count results in a moving 366 day window and input that into this.

Thanks in advance for any advice/help.

SAMPLE.xlsx

Here is a densensitised copy of the basic workbook I am working on, all private/confidential information removed.