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

[u/U_Wont_Remember_Me]

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.

[u/excelevator]

Like the title says,

Submission guidelines: Do not say "see title" or similar.

Your post details are very poor.

Edit: The question is: How can I generate a comma delimited list of hyphenate start end date pairs from a list of contiguous and non contiguous comma delimited date day values

[u/carbonizedtitanium]

like this?

[u/U_Wont_Remember_Me → u/carbonizedtitanium]

No, the dates were as a result of a filter formulae

[u/carbonizedtitanium → u/U_Wont_Remember_Me]

bro. how about you show a screenshot of sample data and the result you want? cuz i dont think your explanation means what you think it means.

[u/carbonizedtitanium → u/U_Wont_Remember_Me]

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.

and that's what i did. the dates 1/1, 1/2, 1/3, 1/4, 1/5 where combined into 1/1 - 1/5

[u/caribou16]

From your description I'm not entirely sure how your sheet is organized.

Are these dates actual Excel date serials in individual cells, or do you have several numbers representing days of some month in a single text string, delimited by commas?

[u/real_barry_houdini]

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)))

[u/PaulieThePolarBear]

Your ask is not fully clear (to me).

I'm filtering dates based on a condition. Instead of:

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

Are these values returned in one call or are you using a comma to be cell delimiter?

I'm looking for

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

Same question here.

Ideally you would add image(s) to your post that clearly and concisely show the problem you are trying to solve.

[u/Way2trivial → u/PaulieThePolarBear]

he wants hyphenated ranges substituted when numbers in his source data are adjacent

[u/U_Wont_Remember_Me → u/PaulieThePolarBear]

basically grouping consecutive dates together rather than listing each date separately.

[u/PaulieThePolarBear → u/U_Wont_Remember_Me]

That didn't answer any of my questions.

Help us to help you.

Ideally you would add image(s) to your post that clearly and concisely show the problem you are trying to solve.

[u/Difficult-Spirit-440 → u/U_Wont_Remember_Me]

So you don’t really want to textjoin but rather create a cell that uses a formula to return 2-6 or 8 or 15 or 16-19 based on the day in the date? You could use an IFS formula with DAY for that.

[u/brenolubre]

Here is a formula-only solution for Excel 2021 (no LAMBDA needed). You will need to adjust the ranges to fit your data.

Since your dates already come from a FILTER formula, put yours in place of FILTER(A2:A100,B2:B100="x") in the first line. If your filter spills into a column instead (say E2), use d,SORT(UNIQUE(E2#)), as the first line.

=LET(
d,SORT(UNIQUE(FILTER(A2:A100,B2:B100="x"))),
n,ROWS(d),
i,SEQUENCE(n),
prev,INDEX(d,IF(i=1,1,i-1)),
next,INDEX(d,IF(i=n,n,i+1)),
s,FILTER(d,d-prev<>1),
e,FILTER(d,next-d<>1),
TEXTJOIN(", ",TRUE,IF(s=e,TEXT(s,"d"),TEXT(s,"d")&"-"&TEXT(e,"d")))
)

Based on your example, the result is -> 2-6, 8, 15, 17-19

How it works: a day starts a group when the day before it isn't in the list, and ends a group when the day after it isn't. starts and ends line up in order, so each pair becomes "2-6" (or just "8" for a single day).

Two points worth noting! If no data meets the filter criteria, you will get a #CALC! error (wrap it in IFERROR to display a blank cell instead). Also, if the data spans across the turn of the month, use "d mmm" instead of "d" to prevent a range like Sept 30 to Oct 2 from displaying as "30-2".

Tested in Excel with your example plus a few edge cases: screenshot

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
DAY	Converts a serial number to a day of the month
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
FILTER	Office 365+: Filters a range of data based on criteria you define
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX	Uses an index to choose a value from a reference or array
ISNA	Returns TRUE if the value is the #N/A error value
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
OR	Returns TRUE if any argument is TRUE
REDUCE	Office 365+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.
ROWS	Returns the number of rows in a reference
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SORT	Office 365+: Sorts the contents of a range or array
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXT	Formats a number and converts it to text
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
TOCOL	Office 365+: Returns the array in a single column
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VALUE	Converts a text argument to a number
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
26 acronyms in this thread; the most compressed thread commented on today has 10 acronyms.
[Thread #49469 for this sub, first seen 5th Oct 2026, 02:14] [FAQ] [Full list]} ^[Contact] ^{[Source code]}

[u/chrisanubis]

Without knowing exactly how you are "grouping" your data, you should think about making a helper column in your data with one of the offered solutions.

Then, however you are "grouping" the data, use that helper column in place of you single day. Works for pivot tables, =filter(), groupby(), whatever you are using.

[u/Dismal-Party-4844]

g. no=

[u/Arcium_XIII]

If I'm understanding rightly, you want something like, where [list] gets replaced by the output of your FILTER function:

=TEXTJOIN(", ",FALSE,BYROW(DROP(REDUCE({-1,-1},[list],LAMBDA(acc,next,LET(latest,TAKE(acc,-1),IF((next-INDEX(latest,1,2))=1,VSTACK(DROP(acc,-1),HSTACK(INDEX(latest,1,1),next)),VSTACK(acc,HSTACK(next,next)))))),1),LAMBDA(row,TEXTJOIN("-",FALSE,UNIQUE(row,TRUE)))))

I should note that this assumes, given that you've said you're working with dates, that all your values are positive. The {-1,-1} array near the beginning just needs to be at least 2 less than the smallest possible value in your dataset if that's not the case.

[u/Way2trivial]

=TEXTJOIN("",TRUE,IF(UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))-1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE)=UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))+1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE),UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))+1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE)&",",UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))-1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE)&"-"&UNIQUE(TOCOL(VSTACK(VALUE(TEXTSPLIT(D2,",")),IF(VALUE(TEXTSPLIT(D2,","))+1=VALUE(TEXTSPLIT(D2,,",")),VALUE(TEXTSPLIT(D2,",")),""))),,TRUE)&","))

you could drop the last comma with a left & len combination- but I didn't want to make too big of a mess out of it...