Display date using ordinal numbering

S

streets

New Member
Joined
Mar 23, 2024
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
It is possible to do this with a single formula, but I am using a lookup table here.

$scratch.xlsm
ABCDE
1Wednesday, September 18th, 20241st
22nd
33rd
44th
55th
66th
77th
88th
99th
100th
Ordinal Day
Cell Formulas
RangeFormula
A1A1=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))
 
Upvote 0
Here is a standalone formula that does not need the table.

Excel Formula: 
=LET(T,TODAY(),TEXT(T,"Dddd, Mmmm")&" "&DAY(T)&MID("thstndrdthththththth",RIGHT(DAY(T),1)*2+1,2)&", "&YEAR(T))
 
Upvote 0
Solution
Another option:

Excel Formula: 
=LET(t, TODAY(), TEXT(t,"dddd, mmmm d")&XLOOKUP(DAY(t),{1;2;3;4},{"st";"nd";"rd";"th"},,-1)&", "&YEAR(t))
 
Upvote 1
6StringJazzer said:
Here is a standalone formula that does not need the table.

Excel Formula: 
=LET(T,TODAY(),TEXT(T,"Dddd, Mmmm")&" "&DAY(T)&MID("thstndrdthththththth",RIGHT(DAY(T),1)*2+1,2)&", "&YEAR(T))
Click to expand...
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.
 
Upvote 0
Mine would not work correctly for 21 22 23... my bad. Here is the corrected formula:

Excel 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
ABC
101/08/2024Thursday, August 1st, 2024Wednesday, September 18th, 2024
202/08/2024Friday, August 2nd, 2024
303/08/2024Saturday, August 3rd, 2024
404/08/2024Sunday, August 4th, 2024
505/08/2024Monday, August 5th, 2024
606/08/2024Tuesday, August 6th, 2024
707/08/2024Wednesday, August 7th, 2024
808/08/2024Thursday, August 8th, 2024
909/08/2024Friday, August 9th, 2024
1010/08/2024Saturday, August 10th, 2024
1111/08/2024Sunday, August 11th, 2024
1212/08/2024Monday, August 12th, 2024
1313/08/2024Tuesday, August 13th, 2024
1414/08/2024Wednesday, August 14th, 2024
1515/08/2024Thursday, August 15th, 2024
1616/08/2024Friday, August 16th, 2024
1717/08/2024Saturday, August 17th, 2024
1818/08/2024Sunday, August 18th, 2024
1919/08/2024Monday, August 19th, 2024
2020/08/2024Tuesday, August 20th, 2024
2121/08/2024Wednesday, August 21st, 2024
2222/08/2024Thursday, August 22nd, 2024
2323/08/2024Friday, August 23rd, 2024
2424/08/2024Saturday, August 24th, 2024
2525/08/2024Sunday, August 25th, 2024
2626/08/2024Monday, August 26th, 2024
2727/08/2024Tuesday, August 27th, 2024
2828/08/2024Wednesday, August 28th, 2024
2929/08/2024Thursday, August 29th, 2024
3030/08/2024Friday, August 30th, 2024
3131/08/2024Saturday, August 31st, 2024
Sheet2
Cell Formulas
RangeFormula
C1C1=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:B31B1=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))
 
Upvote 0
=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"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Excel formula to read prior quarter as "ABC Corp Q (#) yyyy taxpayment"?
Replies
9
Views
224
awoohaw
awoohaw
J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
1K
Jaxi
J
Y
  • Question
How to have a formula auto update based off the number of filled rows.
Replies
0
Views
429
Yaeger38
Y
C
  • Question
Create code that sends emails and creates a log
Replies
1
Views
199
cokeoverpepsi123
C
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
2K
felixstraube
F

Forum statistics

Threads
1,221,526
Messages
6,160,340
Members
451,637
Latest member
hvp2262

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top