How to join text as a date

[kofi11]

Hi,

I have a large dataset where each case reference may have multiple lines. For example showing which staff member made a visit.

I have been asked to join these staff members in a single cell so I use the following formula:

=IF(A2=A1," ",TEXTJOIN(", ",TRUE,OFFSET(P2,0,0,COUNTIF(A:A,A2,1)))

Column A holds all case references. Column P holds the visit staff.

This works fine. But when asked to do the same and join all the visit dates, I just get the 5 digits numbers instead of a string of dd/mm/yyyy dates separed by commas.

The number format is already set to date but the dates still appear as 5 digit numbers.

Can anybody suggest a solution please?

 

[Georgiboy]

You could try this:

Book1
	A	B	C
1	Case	Visit Date	Joined Visit Date
2	4
3	5	20/06/2024	20/06/2024
4	7
5	8
6	9
7	10
8	11
9	12		00/01/1900, 19/06/2024
10	12	19/06/2024
11	14
12	17
13	18		00/01/1900, 00/01/1900, 19/06/2024, 20/06/2024, 21/06/2024, 00/01/1900
14	18
15	18	19/06/2024
16	18	20/06/2024
17	18	21/06/2024
18	18
19	20
Sheet1

Cell Formulas
Range	Formula
C2:C19	C2	=LET(a,$A$2:$A$19,b,$B$2:$B$19,IF(OR(AND(COUNTIF(a,A2)=1,B2=""),A2=A1),"",ARRAYTOTEXT(TEXT(FILTER(b,(a=A2)),"dd/mm/yyyy"))))

 

[kofi11]

Thanks both, worked a treat. Really appreciate all the help and responses

 

[Fluff]

Glad we could help & thanks for the feedback.