Adding ordinals (st nd rd th) to date when using TEXT etc

[scotthannaford1973]

I have a formula that combines some text and the date of the Monday in the current week - ="Digital Programme - "&TEXT((TODAY()-MOD(TODAY()-2,7)),"dd mmmm yyyy") as below; is there any way to add st, nd etc to the date - so 25th instead of 25 etc?


TIA

 

[awoohaw]

try this:

Book2
	A	B	C	D
1	Test Date
2
3	2024-03-01		Digital Program= 1st March 2024	Digital Program= 25th March 2024
4	2024-03-02		Digital Program= 2nd March 2024
5	2024-03-03		Digital Program= 3rd March 2024
6	2024-03-04		Digital Program= 4th March 2024
7	2024-03-05		Digital Program= 5th March 2024
8	2024-03-06		Digital Program= 6th March 2024
9	2024-03-07		Digital Program= 7th March 2024
10	2024-03-08		Digital Program= 8th March 2024
11	2024-03-09		Digital Program= 9th March 2024
12	2024-03-10		Digital Program= 10th March 2024
Sheet3

Cell Formulas
Range	Formula
D3	D3	="Digital Program= " & TEXT(TODAY(),"d") & SWITCH(0+RIGHT(DAY(TODAY()),1),1,"st",2,"nd",3,"rd","th") & TEXT(TODAY()," mmmm yyyy")
A3	A3	=DATE(2024,3,1)
A4:A12	A4	=A3+1
C3:C12	C3	="Digital Program= " & TEXT(A3,"d") & SWITCH(0+RIGHT(DAY(A3),1),1,"st",2,"nd",3,"rd","th") & TEXT(A3," mmmm yyyy")

 

[scotthannaford1973]

="Digital Program= " & TEXT(TODAY(),"d") & SWITCH(0+RIGHT(DAY(TODAY()),1),1,"st",2,"nd",3,"rd","th") & TEXT(TODAY()," mmmm yyyy")

thanks - would never have worked that out!

 

[awoohaw]

Happy to help. Best Wishes!

 

[dreid1011]

Might look at the dates of 11, 12 and 13 with this formula.

 

[awoohaw]

Might look at the dates of 11, 12 and 13 with this formula.

good catch. Why is the English language so irregular!

Book2
	A	B	C	D
1	Test Date
2
3	2024-03-01		Digital Program= 1st March 2024	Digital Program= 25th March 2024
4	2024-03-02		Digital Program= 2nd March 2024
5	2024-03-03		Digital Program= 3rd March 2024
6	2024-03-04		Digital Program= 4th March 2024
7	2024-03-05		Digital Program= 5th March 2024
8	2024-03-06		Digital Program= 6th March 2024
9	2024-03-07		Digital Program= 7th March 2024
10	2024-03-08		Digital Program= 8th March 2024
11	2024-03-09		Digital Program= 9th March 2024
12	2024-03-10		Digital Program= 10th March 2024
13	2024-03-11		Digital Program= 11th March 2024
14	2024-03-12		Digital Program= 12th March 2024
15	2024-03-13		Digital Program= 13th March 2024
16	2024-03-14		Digital Program= 14th March 2024
17	2024-03-15		Digital Program= 15th March 2024
18	2024-03-16		Digital Program= 16th March 2024
19	2024-03-17		Digital Program= 17th March 2024
20	2024-03-18		Digital Program= 18th March 2024
21	2024-03-19		Digital Program= 19th March 2024
22	2024-03-20		Digital Program= 20th March 2024
23	2024-03-21		Digital Program= 21st March 2024
24	2024-03-22		Digital Program= 22nd March 2024
25	2024-03-23		Digital Program= 23rd March 2024
26	2024-03-24		Digital Program= 24th March 2024
27	2024-03-25		Digital Program= 25th March 2024
28	2024-03-26		Digital Program= 26th March 2024
29	2024-03-27		Digital Program= 27th March 2024
30	2024-03-28		Digital Program= 28th March 2024
31	2024-03-29		Digital Program= 29th March 2024
32	2024-03-30		Digital Program= 30th March 2024
33	2024-03-31		Digital Program= 31st March 2024
Sheet3

Cell Formulas
Range	Formula
D3	D3	="Digital Program= " & TEXT(TODAY(),"d") & IF(AND(DAY(TODAY())>=11,DAY(TODAY())<=13),"th",SWITCH(0+RIGHT(DAY(TODAY()),1),1,"st",2,"nd",3,"rd","th")) & TEXT(TODAY()," mmmm yyyy")
A3	A3	=DATE(2024,3,1)
A4:A33	A4	=A3+1
C3:C33	C3	="Digital Program= " & TEXT(A3,"d") & IF(AND(DAY(A3)>=11,DAY(A3)<=13),"th",SWITCH(0+RIGHT(DAY(A3),1),1,"st",2,"nd",3,"rd","th")) & TEXT(A3," mmmm yyyy")