+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2010

    Question 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
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    North Carolina
    MS-Off Ver

    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.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count Consecutive Days (with rule associated)
    By DUNC78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2015, 10:18 AM
  2. Replies: 1
    Last Post: 02-26-2015, 06:12 PM
  3. [SOLVED] Count Consecutive Days Based on Unique Number
    By Kimston in forum Excel General
    Replies: 5
    Last Post: 11-23-2014, 04:46 PM
  4. Months in a year based on number of days
    By managingcrap in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-23-2014, 10:58 AM
  5. [SOLVED] Display cells 12 months prior and 12 months post from a given date
    By hog77 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2012, 06:25 AM
  6. Replies: 3
    Last Post: 08-16-2012, 09:38 AM
  7. Replies: 1
    Last Post: 04-29-2005, 03:06 PM


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