Getting Consecutive Days Worked

Question

Hi All,

I am working on report for which I need to calculate the number of days worked consecutively by the staff.

So I have a table with columns like Staff ID, Name & Date of Shift. Now I would like to get the consecutive days worked column from the same.
I have a demo table of how the hours worked by staff would look.

Sum of Sum of Hours	Column Labels
Row Labels	45536	45537	45538	45539	45540	45541	45542	45543
A1	10	7.5	7.75	6.25	8.75
A2		6	5.5				6.5	2
A3		5	2.75	4.5	4.5	4
A4	4.5			5	6.5	7	8	5
A5	5.75	6.75	5		7.75	9		9
A6				5	5.25	6.75	10.25	8.5
A7		4.5	4.5
A8

And looking at the table, I need the days to be calculated. I've added the desired result as well.

Sum of Sum of Hours	Column Labels
Row Labels	45536	45537	45538	45539	45540	45541	45542	45543
A1	1	2	3	4	5
A2		1	2				1	2
A3		1	2	3	4	5
A4	1			1	2	3	4	5
A5	1	2	3		1	2		1
A6				1	2	3	4	5
A7		1	2
A8

I have been struggling to get the consecutive days worked. especially resetting the count after a break.

Any help would be appreciated.

Thank you very much.

Kind Regards,

Shreyas

Answer 1

Could you share a test file? Then I can have a try on it.

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.

*Please make sure you have removed any sensitive or private information in the sample file before uploading.