Count consecutive dates as one occurrence

The sheet is protected so I can't do anything with it. However I suggest a helper column for each person with a formula like:
=IF(AND(NOT(ISBLANK(C2)),C2<>C1),1,0).

This formula checks to see that cell is not blank and that it is different than the cell above it. If this is true, then it is the start of an occurrence. You can then use this helper column in a SUMIFS formula to count absences by type. The helper columns can be hidden.

This proposed solution modifies dflak's formula so that it reads:

Formula:

=IF(SUM(D$2:D2)<=40,0,IF(AND(NOT(ISBLANK(C2)),C2<>C1),1,0))

The array entered formula** that populates column G on the summary sheet is*:

Formula:

=SUM(IF(CALENDAR!$A$2:$A$1827>=EDATE(B$4,-12),IF(CALENDAR!$A$2:$A$1827<B$4,OFFSET(CALENDAR!C$1,1,2+3*(ROW(A1)-1),1826))))

*Assumes that the names are in the same order as row one of the calendar sheet.
** Array entered formulas are activated by simultaneously pressing Ctrl, Shift and Enter while the cell is in edit mode.
Edit: the second formula has been changed in post and in the file to take dates into account.
Let us know if you have any questions.

I tried it differently.

If the only case counted is for Employee Illness then change the formula in E2 and down of the Calendar tab so that it reads:

Formula:

=IF(SUM(D$2:D2)<=40,0,IF(AND(C2="Employee Illness",C2<>C1),1,0))

Let us know if you have any questions.

If you are only counting Employee Illness hours in column D ignoring the hours of other absences in the >40 parameter, this might work for you. If the 40 is a count of all hours then you already have a solution, I think, from JeteMc.

Formula:

=IF(SUMIF(C$2:C2,"Employee Illness",D$2:D2)<=40,0,IF(AND(C2="Employee Illness",C2<>C1),1,0))

Lets try the following formula in Calendar E2 and down as applied to a copy of the file that was attached to post #1 and had hours per day (not cumulative):

Formula:

=IF(SUM(INDIRECT(ADDRESS(MATCH(EDATE(SUMMARY!B$4,-12),CALENDAR!A$2:A$1827,0)+1,4,2)):D2)<=40,0,IF(AND(C2="Employee Illness",C2<>C1),1,0))

I tested with a dates of 1/2/18 and 1/19/18 in Summary B4 and it gave a expected values (1 and 0) in Summary G7 although the 1/19/18 date did cause Calendar E2 to display 1 (but that wouldn't be counted by the formula in Summary G7). I hope that made sense.
Let me know if you have any questions.

I created a table out of your data on the Calendar worksheet, created names from the headers and inserted helper columns E and H (these would have to be repeated for each person)
This is entered in Calendar!E2 and filled down. The equivalent is filled in all the helper columns with the proper reference changes.

Formula:

=IF(SUMIF(F$2:F2,"Employee Illness",G$2:G2)<=40,0,IF(AND(NOT(ISBLANK(F2)),F2<>F1),1,0))

Enter in Summary!G7 and fill down

Formula:

=SUMIFS(OFFSET(INDIRECT(SUBSTITUTE(A7,", ","__")),,2),Table1[Date],"<"&$B$4,Table1[Date],">="&EDATE(SUMMARY!$B$4,-12))

The INDIRECT(SUBSTITUTE(A7,", ","__")) uses the name on the summary worksheet and converts it to match the names in the header of the Table on CALENDAR worksheet.

I got a little further, but not yet at the end...........
Please check if this is going in the right direction.

Originally Posted by Bchez

Thank you PietBom. I tried to also stay on the Summary tab but I didn't use helper columns at first. Thank you for helping me, I really appreciate you.

I worked on the formulas and came 1 step further.
In column N you find the formula to calculate the row number where the sum reaches 40 or more.
This formula works fine if it counts all hours regardless the criteria Illness.
But the rest seems to work.
Still more challenges

Here is another attempt as I think there was an error in that if the first consecutive absences totalling 40 are not being correctly calculated.
On the Calendar worksheet E2 enter this formula and fill down.

Formula:

=IF(D2="","",IF(SUMIF(C$2:C2,"Employee Illness",D$2:D2)<=40,"",IF(AND(C2="Employee Illness",C2<>C1),1,IF(AND(C2<>C3,C2="Employee Illness"),1,""))))

Here is a modification to the formula that can be pasted into E2 and that has been tested on the set up shown in the top screen shot (employee illness 1/13/17 - 1/17/17) and yields the expected 1 in E18:

Formula:

=IF(SUM(INDIRECT(ADDRESS(MATCH(EDATE(SUMMARY!$B$4,-12),CALENDAR!$A$2:$A$1827,0)+1,4,2)):D2)<=40,0,IF(AND(C2="Employee Illness",OR(SUM(INDIRECT(ADDRESS(MATCH(EDATE(SUMMARY!$B$4,-12),CALENDAR!$A$2:$A$1827,0)+1,4,2)):D1)<40,C2<>C1)),1,0))

Let us know if you have any questions.

One more try. I still use a helper column but this time, the helper column is tied to the date on the Summary worksheet. As the date is changed, the earliest date calculated by the date on the Summary worksheet is used to determine the starting date of the list of dates on the CALENDAR worksheet. The 40 hours then comes into play.
Formula for CALENDAR column E. Enter in E2 and fill down (if in a table this will auto fill)

Formula:

=IF(D2="","",IF(SUMIFS($D$2:$D$1827,$A$2:$A$1827,"<="&A2,$A$2:$A$1827,">="&EDATE(SUMMARY!$B$4,-12))<=40,"",IF(AND(C2="Employee Illness",C2<>C1,D3=""),1,IF(AND(C2<>C3,C2="Employee Illness"),1,""))))

I have copied the supplied data and then pasted several times down the CALENDAR worksheet to give data to work with.
If the dates on the CALENDAR worksheet are less than the date determined on the SUMMARY worksheet, column E will be blank for those dates.

I have copied the formula to new helper columns for the next 2 employees. This workbook is slow if you don't have a fast computer so calculations appear to not complete for a while. What you can do is not have dates into the future if you can help it. This will eliminate a lot of unnecessary calculation.

Now I finalized it: The formula in N7 counts the Illness hours exceeding 40.
The rest was already working to my opinion.
I hope this is what you wanted.

Did msg#22 come anywhere close to what you were after?

Thank you for the feedback. This is a unique of attendance keeping which is quite interesting. It will take a lot of testing to see if this method of solving the problem will stand up.

Hope you are feeling better.

Hi Bchez,
I think I put the formula in Summary!C7 instead of G7
What was originally the difference between expected results in Summary!C7 and G7 ?
I will wait until you have tested the different solutions.