Spreadsheet: 169269d1342761246-count-sick-day-occurrences-based-on-dates-and-the-duration-of-each-occurrence-in-hours-sick-day-incidents-test-2
Sheet: Sheet1

EE IDN | Sick Date | Sick Hours on Sick Date | # incidents (manual) | # hours in that incident (manual) | Help1 | Help2 | Workday | Incident | Incident Number |  |  | to count the number or sick occurences per employee #
33567 | 2012-03-19 00:00:00 | 7.5 | 1 |  | Monday | not weekend - 1 separate occurrenc |  |  | 1
33567 | 2012-03-22 00:00:00 | 7.5 | 0 |  | Thursday |  |  |  |  |  |  | one sick occurrence equals consecutive sick days off work
33567 | 2012-03-23 00:00:00 | 7.5 | 1 |  |  | a new separate occurrence |  |  |  |  |  | one sick occurrence could be separated by 2 days (a weekend) off work, usually a Friday and Saturdy
54321 | 2011-07-20 00:00:00 | 7.5 | 1 |  |  |  |  |  |  |  |  | for each occurrence for each emmployee, count the # hours in each occurrence
54321 | 2011-10-03 00:00:00 | 7.5 | 1 |  |  |  |  |  |  |  |  | I have tried this formula to get a count of occurrences per employee #:
54321 | 2011-10-18 00:00:00 | 7.5 | 0 |  | no weekend |  |  |  |  |  |  | =IF(AND(A3=A2,(OR(B3=B2+1,B3=B2+3))),0,1)
54321 | 2011-10-19 00:00:00 | 7.5 | 0 |  | no weekend |  |  |  |  |  |  | But I now need to count the duration in #hours of each occurrence for each employee
54321 | 2011-10-20 00:00:00 | 4 | 0 |  | no weekend
54321 | 2011-10-21 00:00:00 | 7.5 | 1 |  | no weekend
55448 | 2011-08-15 00:00:00 | 7.5 | 0
55448 | 2011-08-16 00:00:00 | 7.5 | 1
55448 | 2011-08-29 00:00:00 | 2 | 1
67894 | 2011-07-21 00:00:00 | 7.5 | 0 |  |  |  |  |  |  |  | Row Labels | Sum of Incident | Sum of Sick Hours on Sick Date
67894 | 2011-07-22 00:00:00 | 7.5 | 0 |  | Friday | weekend - count within 1 occurrence |  |  |  |  | 33567 | 2 | 22.5
67894 | 2011-07-25 00:00:00 | 7.5 | 0 |  | Monday | weekend - count within 1 occurrence |  |  |  |  | 1 | 1 | 15
67894 | 2011-07-26 00:00:00 | 3.5 | 0 |  |  |  |  |  |  |  | 2 | 1 | 7.5
67894 | 2011-07-27 00:00:00 | 7.5 | 0 |  |  |  |  |  |  |  | 54321 | 3 | 41.5
67894 | 2011-07-28 00:00:00 | 7.5 | 0 |  |  |  |  |  |  |  | 3 | 1 | 7.5
67894 | 2011-07-29 00:00:00 | 7.5 | 1 |  |  |  |  |  |  |  | 4 | 1 | 26.5
99544 | 2011-10-04 00:00:00 | 7.5 | 0 |  |  |  |  |  |  |  | 5 | 1 | 7.5
99544 | 2011-10-05 00:00:00 | 7.5 | 1 |  |  |  |  |  |  |  | 55448 | 2 | 17
99544 | 2011-12-06 00:00:00 | 7.5 | 1 |  |  |  |  |  |  |  | 5 | 0 | 7.5
99544 | 2012-01-12 00:00:00 | 7.5 | 0 |  |  |  |  |  |  |  | 6 | 1 | 7.5
99544 | 2012-01-13 00:00:00 | 6.5 | 1 |  |  |  |  |  |  |  | 7 | 1 | 2
99544 | 2012-01-16 00:00:00 | 7.5 | 0 |  |  |  |  |  |  |  | 67894 | 1 | 48.5
99544 | 2012-01-17 00:00:00 | 7.5 | 0 |  |  |  |  |  |  |  | 7 | 0 | 41
99544 | 2012-01-18 00:00:00 | 7.5 | 1 |  |  |  |  |  |  |  | 8 | 1 | 7.5
 |  |  |  |  |  |  |  |  |  |  | 99544 | 3 | 59
 |  |  |  |  |  |  |  |  |  |  | 8 | 0 | 7.5
 |  |  |  |  |  |  |  |  |  |  | 9 | 1 | 7.5
I need to count the number of CONSECUTIVE days to = 1 indicident |  |  |  |  |  |  |  |  |  |  | 10 | 1 | 36.5
If there is a Friday & Monday sick, then this is still 1 consecutive incident even though there is a gap in the dates |  |  |  |  |  |  |  |  |  |  | 11 | 1 | 7.5
I need to also add up the total # hours in each incident |  |  |  |  |  |  |  |  |  |  | Grand Total | 11 | 188.5