Find 7 th Consecutive worked Day

[dkma]

Hi all, 

How to find if there are 7 consecutive working days in a Row. 

11111	8/01	Yes
11111	8/02	Yes
11111	8/03	No
11111	8/04	No
11111	8/05	yes
11111	8/06	Yes
11111	8/07	Yes
11111	8/08	Yes
11111	8/09	Yes
11111	8/10	Yes
11111	8/11	Yes
11111	8/12	Yes
11111	8/13	yes
11111	8/14	No
11111	8/15	No
11111	8/16	No
11111	8/17	Yes
11111	8/18	Yes
11111	8/19	No

Kindly help

[AngelosPachis]

Hey @dkma ,

Can you elaborate a bit more on what the columns in the table stand for?

Is 8/01 a date? So the 1st of August? If yes, for which year? Also, what yes.no stands for? Is it the desired output, or is it a flag of whether that employee has worked that day or not?

[mnmemilymnm]

I take it that "Yes" means the person worked, and "No" means they didn't? Let's call that column [Worked].

I recommend using a multi row formula tool to create a ticker that starts with 1 on the first day worked, increases by 1 for every subsequent day worked, and resets back to 1 next time the person works after having taking some time off.

In the configuration of the Multi Row Formula tool, create a new field called Ticker. Set Value for Rows that Don't Exist to 0 or Empty. If you have multiple employees (more than just employee #1111), then Group By Employee.

The formula will look something like this:

if([Date]="No")

then 0

elseif(Row:[Worked]="Yes" and (Row-1:[Worked]="No" or Row-1:[Worked]=0))

then 1

elseif(Row-1:[Worked]="Yes")

then Row-1:[Ticker]+1

else Null()

endif

You may have to play with the formula a bit, but that's the general idea.

Then you can filter for [Ticker]>=7, and those will be the dates where the employee is on their 7th (or 8th or 9th, etc.) day of working.

[jdunkerley79]

I would suggest:

First, parse the dates

You can then filter the days not worked

Then final sort into ID, Date ascending

Finally a multi-row formula tool can confirm that the last 7 days were all worked for an ID:

Have attached a quick sample

[apathetichell]

Stylistically I don't ever use more than say 2 in the multi-row formula. I don't really know why.  I like a Tile tool approach/unique value approach.

[Qiu]

@dkma 
Batch macro should be a good match for this case.