Count Absences by ID Restart Count when Count of days between dates is greater than 30

[cdobbins44]

This is my first post - I hope I did not overly complicate this and I greatly appreciate your help. Thank you.

THIS IS NOT A ROLLING COUNT

Objective: Count days between dates as long as student number is the same and the difference between dates is less than 30 days from the first date in the 30 day window - if greater than 30 days than restart count.

Said differently: as long as the student id is the same and the difference between dates is less than or equal to 30 days then count date absent and if greater than 30 days restart count.

I need to find out if a student was absent 4 or more times within a 30 day window. The 30 day window restarts every time the sum of days between dates exceeds 30 days for each ID.



Notes: I would like to accomplish this in 1 formula. The purple columns show how I am trying to break the formula down in to parts which I can manage.

Column C - "Restart Count@New_ID" restarts the count every time an id changes. In effect this is a count of total absences for each student

Column D - "days apart" shows the days difference between the previous date and current row date

Column E - "Reset_Count_To_1_IF_days apart greater than 30" resets count to 1 if the "days" apart is greater than 30 and resets the count to 1 when the ID changes

Column F - "Answer" shows what the count should look like