How to create either a conditional formatting and/or use IF(AND) Formula for Attendance Tracker?

aIsei

So I'm trying to create a formula or create conditional formatting for attendance. Every 1 Unexcused Absence within 45 days is 1 Occurrence that must be documented with the employee. Every 2 late arrivals/ tardies within 30 days count as 1 Occurrence that must be documented with the employee. So if my employee was Unexcused Thursday then Tardy on Next week Friday and Saturday then they now have 1 occurrence. However, the tricky part is that if they have good attendance for the next 6 months then the occurrence drops back down to zero. If the employee has another Unexcused absence within 45 days of the first occurrence that would be considered their 2nd occurrence. I'm very confused on how to figure out the rolling dates.

Paul Newcome

What happens if they get a second occurrence in (for example) 63 days? You say that anythign within 45 days continues the count, but they reset to zero at (roughly) 180 days. How does that gap between resetting and adding another occurrence work?