Date sequence based on duplicated ID

[DanielCarro]

Hi Guys,

Need some assistance on this one.

I have the following data:

ID	Month Record	Date
2	1	2025-02-03
2	2	2025-02-04
2	3	2025-02-05
3	10	2025-02-14
4	1	2025-02-03
5	13	2025-02-19
5	14	2025-02-20
5	15	2025-02-21
6	6	2025-02-10
6	7	2025-02-11
6	9	2025-02-13
6	10	2025-02-14

Expected Output

ID	FIRST DATE	LAST DATE
2	2025-02-03	2025-02-05
3	2025-02-14	2025-02-14
4	2025-02-03	2025-02-03
5	2025-02-19	2025-02-21
6	2025-02-10	2025-02-11
6	2025-02-13	2025-02-14

Much appreciated any assistance

Kind Regards,

Dan

[Luke_C]

Hi @DanielCarro 

The summarize tool should be able to do this for you. Depending on your definition of first/last, you will want to use the min max or first last options in the tool. The former is based on the dates themselves regardless of order, the latter is based on the order they appear in the data. 

[DanielCarro]

Hi @Luke_C ,

There is a logic. For example, ID 6, it has a sequence 6 -7 which needs the first date 10/02/2025 to 11/02/2025 and for ID 6 which needs first date 13/02/2025 - 14/02/2025. The month record is the consecutive day sequence.

In some cases, you just have one day which will be the same day, for example with ID3

[Luke_C · Accepted solution]

Hi @DanielCarro 

Thanks for clarifying the logic. I've made some inferences but a multi-row tool to check for consecutive months should get this to work:

The formula checks to see if the month is sequential for each ID otherwise it assigns a new sequence. 

[DanielCarro]

Hi @Luke_C ,

It worked perfectly. Thank you