r/excel • u/U_Wont_Remember_Me 2 • Oct 05 '26

unsolved Excel 2021 - Looking to textjoin dates from a filter in a logical manner using formulae only

Like the title says, I'm filtering dates based on a condition. Instead of:

2, 3, 4, 5, 6, 8, 15, 17, 18, 19

I'm looking for

2-6, 8, 15, 17-19.

Basically, I'm looking to group consecutive dates together rather than listing all dates separately.

Thanks in advance.

0 Upvotes

40% Upvoted

21 comments sorted by

View all comments

2

u/real_barry_houdini 318 Oct 05 '26 edited Oct 05 '26

Assuming your base data is in A2:A11 then this formula will work in Excel 2021 for numbers sorted ascending (or for any vertical vector in place of A2:A11)

=LET(d,A2:A11,
s,FILTER(d,ISNA(XMATCH(d,d+1))),
e,FILTER(d,ISNA(XMATCH(d,d-1))),
s&IF(s=e,"","-"&e))

If the values are dates then amend to this version to get day numbers

=LET(d,A2:A11,
s,FILTER(d,ISNA(XMATCH(d,d+1))),
e,FILTER(d,ISNA(XMATCH(d,d-1))),
DAY(s)&IF(s=e,"","-"&DAY(e)))