Convert filter output to date

[sharshra]

I'm using textjoin & filter functions to get the dates when an account appears in the report. Output dates are in number format, but I need it in dd-mmm-yy format. I have done the formatting in dd-mmm-yy format, but still the output is in numbers (stored as text?). Any suggestions to fix this?

Formula in column F -

=TEXTJOIN(",",TRUE,FILTER($B$3:$B$18,$C$3:$C$18=$E5))

Note:
Data shown below is for illustration purpose only. Actual data runs into tens of thousands of rows & huge range of dates, accounts & other details.
Column F is formatted in dd-mmm-yy format.

(For some reason, XL2BB is not working. When I select the cells & click on mini sheet, I get a confirmation message saying that mini sheet is copied to clipboard successfully. But, when I paste in the post, nothing is getting pasted. Tried this several times, but didn't work. Hence, pasting the image & formula.)

 

[Peter_SSs]

Formula in column F -

=TEXTJOIN(",",TRUE,FILTER($B$3:$B$18,$C$3:$C$18=$E5))

Try instead

=TEXTJOIN(",",TRUE,TEXT(FILTER($B$3:$B$18,$C$3:$C$18=$E5),"dd-mmm-yy"))

 

[sharshra]

Thanks, Peter

 

[Peter_SSs]

You're welcome.

When I select the cells & click on mini sheet, I get a confirmation message saying that mini sheet is copied to clipboard successfully. But, when I paste in the post, nothing is getting pasted.

Nothing at all gets pasted?

What happens if you Paste into, say, Notepad instead? Do you get any code pasted?

 

[sharshra]

It is working now , but nothing different was done. Followed the same procedure - select the range, click on mini sheet, click OK in the message box & paste in this forum.

It happened before also - working & not working sometimes. When it doesn't work, it doesn't paste in anything in any application (notepad, word, ppt etc).

date issue.xlsx
	B	C	D	E	F
2	Date	Account		Unique account	Dates
3	01-Dec-21	12345		12345	1-Dec-21,2-Dec-21,3-Dec-21
4	01-Dec-21	23456		23456	1-Dec-21,3-Dec-21
5	01-Dec-21	34567		34567	1-Dec-21,3-Dec-21
6	01-Dec-21	45678		45678	1-Dec-21,3-Dec-21
7	01-Dec-21	56789		56789	1-Dec-21,2-Dec-21
8	02-Dec-21	12345		67890	2-Dec-21,3-Dec-21
9	02-Dec-21	56789		78901	3-Dec-21
10	02-Dec-21	67890		89012	3-Dec-21
11	03-Dec-21	78901		90123	3-Dec-21
12	03-Dec-21	89012
13	03-Dec-21	12345
14	03-Dec-21	90123
15	03-Dec-21	34567
16	03-Dec-21	45678
17	03-Dec-21	67890
18	03-Dec-21	23456
Sheet1

Cell Formulas
Range	Formula
E3:E11	E3	=UNIQUE(C3:C18)
F3:F11	F3	=TEXTJOIN(",",TRUE,TEXT(FILTER($B$3:$B$18,$C$3:$C$18=$E3),"d-mmm-yy"))
Dynamic array formulas.