Hi I am hoping someone can help solve this with a formula.
I need to calculate occurrences based on a 12 month rolling history, while accounting for consecutive occurrences. The live workbook has the data located in the tab TAFW Detail with several scorecard tabs for each person.
On the tab titled Bowman,Aaron within row 11 (Unplanned Occurrences) I need to calculate the number of occurrences the employee has accrued for the 12 months history up to the date or text reference located in rows 2 and 3.
I have manually populated row 11 (unplanned occurrences) with the anticipated results based on the data in the TAFW Detail tab. Within the TAFW details tab column I (Impact) a text notation of Unplanned time equals an event that acquires an occurrence and in column E (Occurrence) is the amount of an occurrence acquired based on the number of hours an employee missed (this is already calculated).
Rules of acquiring occurrences
1.) Occurrences that happen over consecutive dates count as 1 occurrence.
a. Employees do not work weekends so an occurrence on a Friday & a Monday are considered consecutive.
2.) Individual occurrences drop off after once the 12 months has passed.
a. For example if an employee has 5 total occurrences where 1 of the occurrences happened on 4/16/15 once it’s 4/16/16 the employees occurrences are now reduced to 4 total occurrences.
3.) How occurrences are calculated
a. Unplanned Time >1 and <4 hours = 0.5 occurrences
b. Unplanned Time > 4 hours = 1 occurrence