Sorting consecutive dates into groups

Question

I have a list of dates. The list has chronological gaps in it, but I'm looking to bring it all into a single cell of date ranges separated by commas, organized by consecutive dates.

Example of the list:

9/22

9/23

9/24

10/1

10/2

10/7

10/21

10/22

So in this example, the final cell would look something like this:

9/22-9/24, 10/1-10/2, 10/7, 10/21-10/22

Any ideas? I'm desperate!

Thank you!

Answer 1

With the dates in A2:A9, use ifs() in an array formula like this:

=arrayformula( 
  regexreplace( 
    textjoin( 
      "", true, 
      ifs( 
        (A2:A9 <> A1:A8 + 1) * (A2:A9 <> A3:A10 - 1), trim(A2:A9) & ", ", 
        (A2:A9 = A1:A8 + 1) * (A2:A9 = A3:A10 - 1), "", 
        (A2:A9 = A1:A8 + 1), trim(A2:A9) & ", ", 
        (A2:A9 = A3:A10 - 1), trim(A2:A9) & "-", 
        true, iferror(1/0) 
      ) 
    ), 
    ", $", "" 
  ) 
)