Daily posts of Excel tips…and other stuff
Here’s a formula that I use to get the ordinal day number from a date.
=DAY(A1)&IF(AND(DAY(A1)>=4,DAY(A1)< =20),"th", CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
There has to be a shorter way than this. What formula do you use?
Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].
David Wasserman:
=DAY(A1) & IF(OR(MOD(DAY(A1)-1,10)>2,INT(DAY(A1)/10)=1),”th”,CHOOSE(MOD(DAY(A1),10),”st”,”nd”,”rd”))
This formula is a tad mathematical but shorter nonetheless.
19 July 2004, 3:11 pmScott:
J-Walk explains this one in a book of his. It doesn’t provide the same result as yours, but seems to work along the same lines.
=TEXT(A1,”mmmm “)&DAY(A1)&IF(INT(MOD(DAY(A1),100)/10)=1, “th”,IF(MOD(DAY(A1),10)=1, “st”,IF(MOD(DAY(A1),10)=2,”nd”,IF(MOD(DAY(A1),10)=3, “rd”,”th”))))&TEXT(A1,”, yyyy”)
Also, suppose you wanted to adapt YOUR formula to show business days, like “7/5/2004″ = 3rd (or something like that). In a filldown of a range of dates, weekend days and holidays would come up blank.
20 July 2004, 8:53 amMatt H:
Great formula, Dick, et al.
Of course, now we’ll want a macro to apply superscript formatting to the the letters following the number. I messed around with one a couple years ago, and got something passable. Then I just memorized the keyboard shortcuts:
Superscript formatting: Alt+O, Alt+E, Alt+E
Subscript formatting: Alt+O, Alt+E, Alt+B
But I don’t think you could ever do this with a formula result–it would have to be a static value. Or could you?!?
20 July 2004, 9:42 amStan Scott:
There are really only 7 “special” days, which depend on their last digit. Everything else ends in “th”. Thus:
=IF(ISNA(MATCH(DAY(A1),{1,21,31,2,22,3,23},0)),”th”,INDEX({”st”,”nd”,”rd”},RIGHT(DAY(A1),1)))
Stan
20 July 2004, 6:57 pmStan Scott:
(anal-retentive Stan) — so my last word on this one is:
=INDEX({”st”,”nd”,”rd”,”th”},MIN(RIGHT(DAY(A1),1),4))
21 July 2004, 9:56 amAndy Miller:
Stan - neither of your formulas work. The first one is incorrect at 32. The second one is incorrect as soon as 11.
21 July 2004, 2:32 pmJon Peltier:
Andy -
The first one works fine. The 32nd rolls over to the 1st, 2nd, 3rd, or 4th of the next month.
- Jon
30 July 2004, 8:09 amDaniel M.:
Also:
=LOOKUP(MOD(DAY(A1),10)*(INT(DAY(A1)/10)1),{0;1;2;3;4},{”th”;”st”;”nd”;”rd”;”th”})
Daniel M.
16 March 2005, 9:33 amDaniel M.:
There’s a “” between the “10)” and “1″
but the blog parser grabbed it
Hoping it will work this time:
=LOOKUP(MOD(DAY(A1),10)*(INT(DAY(A1)/10) 1),{0;1;2;3;4},{”th”;”st”;”nd”;”rd”;”th”})
Daniel M.
16 March 2005, 9:36 amStewart C.:
I was looking for a formula to add the ending to a number rather than a date, here is the the formula I used for numbers greater than 0:
=IF(OR(MOD(A1-1,10)>2,MOD(INT(A1/10),10)=1),”th”,INDEX({”st”,”nd”,”rd”},MOD(A1,10)))
1 August 2005, 5:32 amDavid:
Does this work? i’m pretty fluent with advanced formulas but the simplicity of this method is appealing.
=MID(”stndrdthththththththththththththththththstndrdthththththththst”,DAY(TODAY())*2-1,2)
21 April 2008, 4:19 pmRobert B:
=CHOOSE(MIN(MOD(A1,10)*(MOD(INT(A1/10),10)1)+1,5),”th”,”st”,”nd”,”rd”,”th”)
Handles any number for which you might want such postfixes (that is, positive integers).
19 July 2008, 8:27 pmRob van Gelder:
=INDEX({"st";"nd";"rd";"th"},MIN(RIGHT(IF(AND(A1>9,A1<14),9,A1)-1)-3,0)+4)
20 July 2008, 4:24 pmRob van Gelder:
or for numbers past 110
20 July 2008, 4:32 pm=INDEX({"st";"nd";"rd";"th"},IF(LEFT(RIGHT(0&A1,2))=”1″,4,MIN(RIGHT(A1-1)-3,0)+4))