Consecutive days but extract the correct rows

DanielCarro

Hi guys,

I am trying to figure this one out.

INPUT							OUTPUT
ID	Type	Date	Days	ConsecutiveDays		ID	Type	Date	Days	ConsecutiveDays
1	Sick	04/07/2024	1	1			1	Sick	05/07/2024	1	2
1	Sick	05/07/2024	1	2			2	Sick	02/07/2024	1	1
2	Sick	02/07/2024	1	1			2	Sick	23/07/2024	1	2
2	Sick	22/07/2024	1	1
2	Sick	23/07/2024	1	2

on the left is the data I have and the output that I want is on the right.

The issue is on ID '2', this individual has been sick on 02/07 and 22/07+23/07, so if there are any consecutive days these are added as in the case of 22 and 23 July; but the 02/07 needs to stay alone as it isn't consecutive.

Any ideas on how to get the right output

Many thanks,

Dan

diegosantos

Hi @DanielCarro.

You can achive this with:

Formula: ToDate(Left(DateTimeAdd([DateTime_Out],1,"days"),10)) = [Row+1:DateTime_Out]

DanielCarro.yxmd