Applying date ordinal to an array of dates.

[HighAndWilder]

I would like to display dates in an array as an ordinal number.

I have an array of dates which could be thousands in numbe :
01/01/2024 02/01/2024 03/01/2024 04/01/2024 05/01/2024

The array could hold the actual day instead of the date.

This will return the ordinal suffix where * represents the day number.
IF(OR(VALUE(RIGHT(DAY(*),2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(DAY(*)))={1,2,3}),CHOOSE(RIGHT(DAY(*)),"st","nd","rd"),"th"))

How do I implement this for each element of the dates / days array?

I thought about having a constant as below and create an array using TEXTSPLIT
1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th,11th,12th,13th,14th,15th,16th,17th,18th,19th,20th,21st,22nd,23rd,24th,25th,26th,27th,28th,29th,30th,31st

and then using INDEX to return the correct element represented by X but I still don't know how to merge the two arrays.
INDEX(TEXTSPLIT("1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th,11th,12th,13th,14th,15th,16th,17th,18th,19th,20th,21st,22nd,23rd,24th,25th,26th,27th,28th,29th,30th,31st",","),X)

Also is there a way to call a UDF passing an array and returning a new / edited array?

Thanks

 

[Eric W]

How about:

Book1
	A	B
1	10/1/2024	1st
2	10/2/2024	2nd
3	10/3/2024	3rd
4	10/4/2024	4th
5	10/5/2024	5th
6	10/6/2024	6th
7	10/7/2024	7th
8	10/8/2024	8th
9	10/9/2024	9th
10	10/10/2024	10th
11	10/11/2024	11th
12	10/12/2024	12th
13	10/13/2024	13th
14	10/14/2024	14th
15	10/15/2024	15th
16	10/16/2024	16th
17	10/17/2024	17th
18	10/18/2024	18th
19	10/19/2024	19th
20	10/20/2024	20th
21	10/21/2024	21st
22	10/22/2024	22nd
23	10/23/2024	23rd
24	10/24/2024	24th
25	10/25/2024	25th
26	10/26/2024	26th
27	10/27/2024	27th
28	10/28/2024	28th
29	10/29/2024	29th
30	10/30/2024	30th
31	10/31/2024	31st
Sheet2

Cell Formulas
Range	Formula
A1:A31	A1	=SEQUENCE(31,,DATE(2024,10,1))
B1:B31	B1	=LET(d,DAY(A1#),d&XLOOKUP(d,{1,2,3,21,22,23,31},{"st","nd","rd","st","nd","rd","st"},"th",0))
Dynamic array formulas.

And yes, you can have a UDF take and output an array.

 

[HighAndWilder]

How about:

Book1
	A	B
1	10/1/2024	1st
2	10/2/2024	2nd
3	10/3/2024	3rd
4	10/4/2024	4th
5	10/5/2024	5th
6	10/6/2024	6th
7	10/7/2024	7th
8	10/8/2024	8th
9	10/9/2024	9th
10	10/10/2024	10th
11	10/11/2024	11th
12	10/12/2024	12th
13	10/13/2024	13th
14	10/14/2024	14th
15	10/15/2024	15th
16	10/16/2024	16th
17	10/17/2024	17th
18	10/18/2024	18th
19	10/19/2024	19th
20	10/20/2024	20th
21	10/21/2024	21st
22	10/22/2024	22nd
23	10/23/2024	23rd
24	10/24/2024	24th
25	10/25/2024	25th
26	10/26/2024	26th
27	10/27/2024	27th
28	10/28/2024	28th
29	10/29/2024	29th
30	10/30/2024	30th
31	10/31/2024	31st
Sheet2

Cell Formulas
Range	Formula
A1:A31	A1	=SEQUENCE(31,,DATE(2024,10,1))
B1:B31	B1	=LET(d,DAY(A1#),d&XLOOKUP(d,{1,2,3,21,22,23,31},{"st","nd","rd","st","nd","rd","st"},"th",0))
Dynamic array formulas.

And yes, you can have a UDF take and output an array.

Thanks Eric.

I didn't realise that one could use XLOOKUP using two arrays like that but it makes sense.