How to track consecutive "streaks" in a table

Hi everyone,

I am trying to find the best way to know if someone has assisted class and therefore is on a streak.

I have a table with the dates they have attended and also have the 52 columns which are the weeks.

I want to be able to know if someone has more than 2 o 3 consecutive weeks?

Here is the link:


See in my sample doc one of probably many different ways to solve this:

Edit: added a switch to exclude strikes if they fall into 2 different years.


Thank u very much Joost, will have a look at it :smiley:


I renamed the document “Strike”, which seems to cover the purpose of this doc a little bit better.

I added a switch to not include a strike period overlapping any years end.

I didn’t like the code in the checkbox column, because it limited the strike period to a pre-programmed period of three week, so I added a page to make the 3 weeks period variable.

The formula is a bit of a mess, but works. I had a hard time figuring to end up with a period of consecutive weeks. Another approach would have been to just check any x week period for compliancy to the objective, but I decided to follow anohter route. It is pretty brute force, so checking a, say, 7 week strike out of thousands of records will take some time to process after making changes or adding rows, but it works.

With some small changes the formula can be adapted to work for different situations.


Thank you very much Joost, it looks great! will play around with it :slight_smile:

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.