Display date using ordinal numbering

[streets]

I desire an excel formula that will automatically update to today's date and that will display the day's digit as ordinal number. How to create it?

Today's date is Wednesday, September 18, 2024. The ordinal numbering endings of "st", "nd", "rd", or "th" is to be added to the end of day's digit, to auto-update to display as "Wednesday, September 18th, 2024" (without quote marks).

When the first of the month comes around, I'd like it to auto-update to Tuesday, October 1st, 2024. Then, following that, automatically update to Wednesday, October 2nd, 2024 and then Thursday, October 3rd, 2024, and so on, as the calendar goes-by.

 

[6StringJazzer]

It is possible to do this with a single formula, but I am using a lookup table here.

$scratch.xlsm
	A	B	C	D	E
1	Wednesday, September 18th, 2024			1	st
2				2	nd
3				3	rd
4				4	th
5				5	th
6				6	th
7				7	th
8				8	th
9				9	th
10				0	th
Ordinal Day

Cell Formulas
Range	Formula
A1	A1	=LET(T,TODAY(),TEXT(T,"Dddd, Mmmm")&" "&DAY(T)&XLOOKUP(VALUE(RIGHT(DAY(T),1)),$D$1:$D$10,$E$1:$E$10)&", "&YEAR(T))

 

[6StringJazzer]

Here is a standalone formula that does not need the table.

=LET(T,TODAY(),TEXT(T,"Dddd, Mmmm")&" "&DAY(T)&MID("thstndrdthththththth",RIGHT(DAY(T),1)*2+1,2)&", "&YEAR(T))

 

[streets]

6StringJazzer, thank you.

 

[felixstraube]

Another option:

=LET(t, TODAY(), TEXT(t,"dddd, mmmm d")&XLOOKUP(DAY(t),{1;2;3;4},{"st";"nd";"rd";"th"},,-1)&", "&YEAR(t))

 

[streets]

felixstraube, awesome. thank you as well.

 

[felixstraube]

Here is a standalone formula that does not need the table.

=LET(T,TODAY(),TEXT(T,"Dddd, Mmmm")&" "&DAY(T)&MID("thstndrdthththththth",RIGHT(DAY(T),1)*2+1,2)&", "&YEAR(T))

I think this formula would not work correctly for day like 11, 12, 13, 21, 22, 23. It would add "st" to 11 for example.

 

[Scott Huish]

Another option:

=LET(t, TODAY(), TEXT(t,"dddd, mmmm d")&XLOOKUP(DAY(t),{1;2;3;4},{"st";"nd";"rd";"th"},,-1)&", "&YEAR(t))

This stops working correctly after 3rd it returns th for everything after that.

 

[felixstraube]

Mine would not work correctly for 21 22 23... my bad. Here is the corrected formula:

=LET(t, TODAY(), TEXT(t,"dddd, mmmm d")&XLOOKUP(DAY(t),{1;2;3;4;21;22;23;24;31},{"st";"nd";"rd";"th";"st";"nd";"rd";"th";"st"},,-1)&", "&YEAR(t))

Book2.xlsx
	A	B	C
1	01/08/2024	Thursday, August 1st, 2024	Wednesday, September 18th, 2024
2	02/08/2024	Friday, August 2nd, 2024
3	03/08/2024	Saturday, August 3rd, 2024
4	04/08/2024	Sunday, August 4th, 2024
5	05/08/2024	Monday, August 5th, 2024
6	06/08/2024	Tuesday, August 6th, 2024
7	07/08/2024	Wednesday, August 7th, 2024
8	08/08/2024	Thursday, August 8th, 2024
9	09/08/2024	Friday, August 9th, 2024
10	10/08/2024	Saturday, August 10th, 2024
11	11/08/2024	Sunday, August 11th, 2024
12	12/08/2024	Monday, August 12th, 2024
13	13/08/2024	Tuesday, August 13th, 2024
14	14/08/2024	Wednesday, August 14th, 2024
15	15/08/2024	Thursday, August 15th, 2024
16	16/08/2024	Friday, August 16th, 2024
17	17/08/2024	Saturday, August 17th, 2024
18	18/08/2024	Sunday, August 18th, 2024
19	19/08/2024	Monday, August 19th, 2024
20	20/08/2024	Tuesday, August 20th, 2024
21	21/08/2024	Wednesday, August 21st, 2024
22	22/08/2024	Thursday, August 22nd, 2024
23	23/08/2024	Friday, August 23rd, 2024
24	24/08/2024	Saturday, August 24th, 2024
25	25/08/2024	Sunday, August 25th, 2024
26	26/08/2024	Monday, August 26th, 2024
27	27/08/2024	Tuesday, August 27th, 2024
28	28/08/2024	Wednesday, August 28th, 2024
29	29/08/2024	Thursday, August 29th, 2024
30	30/08/2024	Friday, August 30th, 2024
31	31/08/2024	Saturday, August 31st, 2024
Sheet2

Cell Formulas
Range	Formula
C1	C1	=LET(t, TODAY(), TEXT(t,"dddd, mmmm d")&XLOOKUP(DAY(t),{1;2;3;4;21;22;23;24;31},{"st";"nd";"rd";"th";"st";"nd";"rd";"th";"st"},,-1)&", "&YEAR(t))
B1:B31	B1	=LET(t, A1, TEXT(t,"dddd, mmmm d")&XLOOKUP(DAY(t),{1;2;3;4;21;22;23;24;31},{"st";"nd";"rd";"th";"st";"nd";"rd";"th";"st"},,-1)&", "&YEAR(t))

 

[Scott Huish]

=LET(t,TODAY(),TEXT(t,"dddd, mmmm d"&LOOKUP(DAY(t),{1,2,3,4,21,22,23,24,31},{"\s\t","\n\d","\r\d","\t\h","\s\t","\n\d","\r\d","t\h","\st"})&", yyyy"))