Count Consecutive Occurrences of Absence including weekends

[Polymorph]

I have a massive data file containing employee hours. They log specific charge numbers that determine the type of work they were doing/if they are absent

Since COVID hit, we have had more employees absent. This is a problem because some of them will have to get re-certified if they exceed a certain amount of absent days (including weekends).

There are around 10 different charge numbers that mean 'absent from facility'. Our task is to determine all the consecutive days that they charged any of those 10 numbers. As soon as an employee reaches 20 consecutive days, we would record it(doesn't really matter how/where we record the names, as long as we have a list of everyone who exceeds 20 days). If the employee shows up for at least one day, then the count would reset. The problem is that we don't log charge numbers for the weekends(Saturday and Sunday) and they have to be included in the count.

I am trying to do this in excel, but have no idea how to include the weekends, when there is no specific record for them. I'm an experienced programmer, I just have no idea where to start or what resources to look at to achieve this. Maybe excel might not be the right place to do this, but any direction is appreciated. (let me know if I am not clear, created an example file below)

[xladept]

Try this: Change the red two to a twenty for a real run. In your sample Zeus was absent twice and Rudyard thrice, consecutively

Please Login or Register  to view this content.

[ddander54]

xladept,

I have 3 questions on this post....
1) I'm not familiar with the ' : ' (colon) throughout the Sub...could you explain?
2) The code is fast and I think I might be able to adapt it for my use.... Could you comment the code so I might be able to understand it and configure it to my situation?
3) Rudyard has 3 consecutive days absent, but when I run the code, K2 = 2, so I'm not sure what its telling me....could you explain?

Thx,
Don

[AliGW]

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread