# Sum Cumulative Unplanned Events Based On Prior 12 Months With Consecutive Days Rule

1. ## Sum Cumulative Unplanned Events Based On Prior 12 Months With Consecutive Days Rule

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

2. ## Re: Sum Cumulative Unplanned Events Based On Prior 12 Months With Consecutive Days Rule

First of all, congratulations on using a table to organize the data! Here is my attempt at it.

I added three helper columns to the table.

Occurrence Count =IF(AND([@Hours]>1,[@Hours]<4),0.5,IF(AND([@Hours]>=4,[@Hours]<=8),1,0)) – this formula agrees with the verbal description but does not necessarily agree with column E.

Consecutive =IFERROR([@[Date of Absence]]=WORKDAY(S1,1),FALSE) – this formula only works if the data is grouped by name and sorted ascending by date of absence. It adds one workday to the previous row and compares it. You might want to add the Holiday option to this formula.

Under 12 =DATE(YEAR([@[Date of Absence]])+1, MONTH([@[Date of Absence]]), DAY([@[Date of Absence]])) > DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())) – this formula adds a year to the date of absence and compares it to the current day.

I also added helper cells to the person page:

In row 4 =DATE(M3,M2,1) – this formula gets the month start day

In row 5 =DATE(M3,M2+1,0) – this formula gets the month end day

I did my formulas on row 15

=SUMIFS(Attendance[Occurrence Count],Attendance[Name],\$A\$3,Attendance[Date of Absence],">="&M\$4,Attendance[Date of Absence],"<="&M\$5,Attendance[Consecutive],FALSE,Attendance[Under 12],TRUE)

The above formula counts the occurrences for the selected name with a date in the indicated month, where the days are not consecutive and the absence is less than a year old.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1