Check next row if date is consecutive

[JeffJeffrey12]

Dear Community,

I am trying to check a set of data where I have multiple starting and end dates for multiple people.

I am sorting the data via "PeopleID" first and then via the "Start" date. Therefore it is sorted properly.

Now I want to check if the "Start"-date of ones PeopleID is consecutive to the "End"-date of the row before that, if its the same PeopleID.

As an example I have the following example data set:

How the raw data is                                                    How I would like it to be

PeopleID	Start	End		PeopleID	Start	End
1	28.09.2020	27.11.2020		1	28.09.2020	27.11.2020
1	10.02.2022	09.04.2022		1	10.02.2022	09.04.2022
1	10.07.2022	09.09.2022		1	10.07.2022	09.09.2022
1	10.12.2022	09.01.2023		1	10.12.2022	09.01.2023
2	05.07.2016	04.08.2016		2	05.07.2016	04.08.2016
2	05.12.2016	04.01.2017		2	05.12.2016	04.01.2017
2	27.02.2022	26.03.2022		2	27.02.2022	26.03.2022
2	27.06.2022	26.07.2022		2	27.06.2022	26.07.2022
2	27.08.2022	26.09.2022		2	27.08.2022	26.09.2022
2	27.10.2022	26.11.2022		2	27.10.2022	26.11.2022
3	02.07.2022	01.08.2022		3	02.07.2022	01.08.2022
3	02.07.2023	01.08.2023		3	02.07.2023	01.08.2023
3	02.11.2023	01.01.2024		3	02.11.2023	01.01.2024
4	21.07.2014	24.01.2016		4	21.07.2014	24.01.2016
4	11.06.2016	14.05.2018		4	11.06.2016	14.05.2018
4	01.10.2019	03.06.2021		4	01.10.2019	03.07.2024
4	04.06.2021	03.06.2022
4	04.06.2022	03.05.2023
4	04.05.2023	03.07.2024

I hope you understand what I am trying to achieve and can help me out on that as I can't find a solution yet.

Thanks in advance!

[Luke_C · Accepted solution]

Hi @JeffJeffrey12 

How does this look?

1. Convert your data to standard dates
2. Sort (just in case)
3. Multi-row to update the start dates for consecutive records
4. Sample the last value for each person/start date (will give the full range)

[JeffJeffrey12]

I am pretty sure, this will solve my problem, but I had to finish other things first, so I had no time to test it with true data yet.

Sorry for taking so much time.

[OllieClarke]

Hi @JeffJeffrey12 

I've got a very similar approach to @Luke_C, but my output takes the first start and last end rather than the last of both:

Hope that helps,

Ollie