Count consecutive dates as one occurrence

[Bchez]

Hi - I have seen similar threads but none that cover what I am needing. I have a spreadsheet with a Calendar Tab that I enter data into to track Employee Illness occurrences. I have a Summary Tab that sums up all the info I need. The spreadsheet is configured to reset every 365 days. What I need is a formula in G7 on the Summary tab that would start when cell D7>40 as 1 occurrence and track subsequent occurrences of Employee Illness from the Calendar tab. The trick is that consecutive dates need to be treated as 1 occurrence. I have attached the spreadsheet with info on the Summary tab that I need. Thank you in advance. I have tried Sumif, Sumproduct. I think it's a simple IF/AND, but I can't get it to work.

[dflak]

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.

[Bchez]

Thanks dflak. If I need the formula to begin when any cell in Col D reaches >40, is would this be hard to write into the formula? BTW I think the password is phs for that sheet. I couldn't get it to open in Excel. Not familiar with how this site handles attachments. I appreciate your suggestion and will try it.

[Bchez]

Doesn't work in this instance but I do like the use of IF(AND(NOT(ISBLANK all in a row!

[JeteMc]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

*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.

[PietBom]

I tried it differently.

[Bchez]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

*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.

So I found a problem with this and I think it's an easy fix, but I can't figure out how to work a second AND into the formula. I have attached the spreadsheet with description.
Password is phs if it is locked. Basically, the formula in Summary G7 should only count Employee Illness, not Tardy, LOA, or Sick Child.

[JeteMc]

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:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Bchez]

This seems to work fine JeteMc. I had too many AND statements in there when I tried it. I will run a test on it. Thank you for your kind help. I can't believe it was so simple!

[Bchez]

So the issue with that one is we have to have a 365 day rolling count for the hours in Col D Calendar tab, based on the date entered in Summary tab B4. When I change the date to 1/2/2018, it gives me an incorrect count in the Summary tab Col G. The count will need to reset to 1 as soon as the hrs >40. I have the formula in the Summary tab D7 but this cell hasn't been used since we created Cols D and E on the Calendar tab.
What is is JeteMc is in Oregon, an employee is allowed 40hrs of sick time per year that won't count as an unexcused employee illness. Once the employee has more than 40 hrs employee illness in any 365 day period, the unexcused illnesses would start at 1. Tricky puzzle

[newdoverman]

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.

Please Login or Register  to view this content.

[Bchez]

Thanks newdoverman - we are only counting employee illness. The catch is it renews every 365 days. We plug a date in cell B4 on the summary tab, and the >40 count has to be within a year of that date. So if we plug in Jan 15, 2018 for example, everything from Jan 14 2017 and before would drop off. So the trick has been threefold: 1. If employee illness >40, start counting them at 1 as unexcused past that point. 2. Count consecutive occurrences of employee illness as 1 unexcused occurrence (So Jan 16,17,18 would be 1 occurrence). 3. Rotate everything every 365 days. I am super impressed with the Excel knowledge on this forum. I was an advanced certified Excel instructor for a long time. Most of the people here know more than I do. Compounding my ignorance, I don't do VBA. But it sure is fun trying stuff and learning new things!

[JeteMc]

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):

Please Login or Register  to view this content.

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.

[newdoverman]

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.

Please Login or Register  to view this content.

Enter in Summary!G7 and fill down

Please Login or Register  to view this content.

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.

[PietBom]

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

[Bchez]

Thank you. I tested it out, and unfortunately it doesn't update when the date in B4 is changed. Specifically, if I enter a date of 1/6/2018, everything before 1/6/2017 should not be counted. The 40 hour OR Safe Sick grace period would start over on 1/6/2017 and new instances should not occur until that number reaches >40. I will try to tweak it a bit ndm. Thank you for all this effort. I had considered using tables but was having issues getting it to reset every 365 days and also start at 1 each time the "current" data reached >40.

[newdoverman]

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.

Please Login or Register  to view this content.

[Bchez]

JeteMc I think I see the problem. If you can open the attached Word doc, I took a couple screen shots. I changed the date in B4 to Jan 6, 2018.
So when the Employee Illness were all in a row (for example Jan13-Jan 17) and Jan 17 was the day that the grace period >40, it didn't count it. But when I moved that last instance of Employee illness from Jan 17 to Jan 18, it created a blank space on Jan 17 and the formula worked. I am so close on this! Trying to figure if the formula just needs to be changed a bit or if I need to add a piece to it. Thank you for sparking this excitement!

[JeteMc]

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:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Bchez]

Thanks PietBom - I think your approach is interesting. I tried to keep it on the Summary tab as well with no luck. I also tried to do it without any helper columns. I appreciate you working on this. Thank you very much!

[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.

[newdoverman]

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)

Please Login or Register  to view this content.

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.

[PietBom]

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

[PietBom]

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.

[newdoverman]

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

[Bchez]

Hey newdoverman - Sorry I've been working on another project and been ill. Yes, this gives me the correct number on the Summary Tab G Column. I am testing scenarios to verify if the data will stay true given different dates. Thanks for your work on this. I noted that you had the instances of unexcused illness accrue at the bottom of a consecutive list instead of the top. I wasn't expecting that, but so far this looks like a very good solution. So grateful!

[newdoverman]

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.

[Bchez]

Thanks I agree. Appreciate the well wishes.

[Bchez]

Hi PietBom - Thank you for working so hard on this. What I actually need is the formula in G7. I think I have it now. Once again, many thanks for your excellent help!

[PietBom]

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.