TEXTJOIN as Text Date Sorts Incorrectly

[Marcusreb]

I need some help please. I have column with names and a column with birthdays. I have created a dynamic calendar where I update the Month and Year and the formula will provide me a list of users with the birthday in that month and year. I need the formula to display in order but the dates are not sorting in correct order.

A1 = Name, B2 = Date, C1 = Month and D1 = Year
Formula is entered in E2 and the formula I have is =TEXTJOIN(CHAR(10),TRUE,(IF(TEXT(B2:B8,"MMMM YYYY")=C1&" "&D1,DAY(B2:B8)&" "&A2:A8,"")))

Matt	1/5/2023​
Dave	1/15/2023​
John	2/1/2023​
Tim	1/4/2023​
Beth	5/5/2023​
Paul	6/6/2023​

Outcome in need the dates to go in order, 4 Tim, 5 Matt, 15 Dave

5 Matt 15 Dave 4 Tim

 

[FormR]

Hi, welcome to the forum! Here is one way you can try:

Book1
	A	B	C	D	E
1	Name	Date	January	2023
2	Matt	01/05/2023			4 Tim
3	Dave	01/15/2023			5 Matt
4	John	02/01/2023			15 Dave
5	Tim	01/04/2023
6	Beth	05/05/2023
7	Paul	06/06/2023
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=LET(f,FILTER(A2:B7,TEXT(B2:B7,"mmmm yyyy")=C1&" "&D1),SORTBY(DAY(CHOOSECOLS(f,2))&" "&CHOOSECOLS(f,1),DAY(CHOOSECOLS(f,2))))
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(f,SORT(FILTER(A2:B8,TEXT(B2:B8,"mmmm yyyy")=C1&" "&D1),2),TEXTJOIN(CHAR(10),,DAY(TAKE(f,,-1))&" "&TAKE(f,,1)))

 

[Phuoc]

Or try this

=TEXTJOIN(CHAR(10),1,IF(TEXT(B2:B7,"mmmmyyyy")=C1&D1,DAY(B2:B7)&" "&A2:A7,""))

 

[Peter_SSs]

Or try this

=TEXTJOIN(CHAR(10),1,IF(TEXT(B2:B7,"mmmmyyyy")=C1&D1,DAY(B2:B7)&" "&A2:A7,""))

That doesn't sort the results as requested ..

I need the formula to display in order but the dates are not sorting in correct order.

@Marcusreb
Welcome to the MrExcel board!

Two more possibilities.
A variation of @Fluff's suggestion in E2 but also including handling the case case of no birthdays in the month.
A variation of @Phuoc's suggestion in F2 to ensure the dates are sorted (Phuoc's formula already handled the case of no birthdays in the month)

23 11 25.xlsm
	A	B	C	D	E	F
1	Name	Date	January	2023
2	Matt	5/01/2023			4 Tim 5 Matt 15 Dave	4 Tim 5 Matt 15 Dave
3	Dave	15/01/2023
4	John	1/02/2023
5	Tim	4/01/2023
6	Beth	5/05/2023
7	Paul	6/06/2023
Birthdays

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN(CHAR(10),,TAKE(SORT(FILTER(HSTACK(DAY(B2:B7)&" "&A2:A7,B2:B7),TEXT(B2:B7,"mmmmyyyy")=C1&D1,{"",1}),2),,1))
F2	F2	=LET(s,SORT(A2:B7,2),d,DROP(s,,1),TEXTJOIN(CHAR(10),1,IF(TEXT(d,"mmmmyyyy")=C1&D1,DAY(d)&" "&TAKE(s,,1),"")))

 

[Marcusreb]

Thank you for the responses much appreciated! I hope you all had a wonderful Thanksgiving!

 

[Fluff]

Glad we could help & thanks for the feedback.