{"id":77,"date":"2011-06-05T06:46:16","date_gmt":"2011-06-05T06:46:16","guid":{"rendered":"http:\/\/greg.lyonkingdom.com\/?p=77"},"modified":"2011-06-06T01:46:33","modified_gmt":"2011-06-06T01:46:33","slug":"ms-excel-a-formula-for-1st-2nd-3rd-etc","status":"publish","type":"post","link":"https:\/\/greg.lyonkingdom.com\/?p=77","title":{"rendered":"MS Excel: a formula for Ordinal Numbers"},"content":{"rendered":"<p>Today I was surprised to find that Microsoft Excel 2010 still doesn&#8217;t have a format option for Ordinal Numbers; dates or numbers such as 1st, 2nd, 3rd, 4th (etc).\u00a0 I wanted to display dates like this: <strong>Sat, Jun 4th<\/strong>.  A quick search revealed some functions that people had written, but I wanted to do it in-cell so I whipped up the following formula&#8230;<\/p>\n<p><code>=TEXT(A2,\"ddd, mmm d\")\u00a0 &amp;<br \/>\nIF(OR(DAY(A2)=1,DAY(A2)=21,DAY(A2)=31),\"st\",<br \/>\nIF(OR(DAY(A2)=2,DAY(A2)=22),\"nd\",<br \/>\nIF(OR(DAY(A2)=3,DAY(A2)=23),\"rd\",\"th\")))<\/code><\/p>\n<p>It seemed like it should have been easier until I realized that 11,12,13 are th&#8217;s.  The first line of this formula does nearly all of the work&#8230;the TEXT function can give <strong>Sat, Jun 4<\/strong>.  The three lines of IF() are just to display st, nd, rd, or th.  This function only works for calendar dates, after 31 it falls apart.<\/p>\n<p>You may find it easier to read if you use carriage returns in lengthy formulas.\u00a0 Simply press ALT+ENTER while editing in a cell.\u00a0 Also you can drag the formula box taller to ease the process.  For more details on Excel&#8217;s TEXT function, look here <a title=\"Excel TEXT function\" href=\"http:\/\/office.microsoft.com\/en-us\/excel-help\/text-function-HP010062580.aspx\">http:\/\/office.microsoft.com\/en-us\/excel-help\/text-function-HP010062580.aspx<\/a> to see the formatting options and examples.\u00a0 Expand the headers in the <em>syntax<\/em> section to see details about numbers, dates and times, currency, strings, etc. that TEXT display.<\/p>\n<p>Let me know if you&#8217;ve found another way to do this.<\/p>\n<p>Hey here are some, and they&#8217;re not limited to calendar days: <a href=\"http:\/\/www.cpearson.com\/excel\/ordinal.htm\">http:\/\/www.cpearson.com\/excel\/ordinal.htm<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I was surprised to find that Microsoft Excel 2010 still doesn&#8217;t have a format option for Ordinal Numbers; dates or numbers such as 1st, 2nd, 3rd, 4th (etc).\u00a0 I wanted to display dates like this: Sat, Jun 4th. A quick search revealed some functions that people had written, but I wanted to do it [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9,8],"tags":[],"_links":{"self":[{"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=\/wp\/v2\/posts\/77"}],"collection":[{"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=77"}],"version-history":[{"count":13,"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=\/wp\/v2\/posts\/77\/revisions"}],"predecessor-version":[{"id":87,"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=\/wp\/v2\/posts\/77\/revisions\/87"}],"wp:attachment":[{"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=77"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=77"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/greg.lyonkingdom.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=77"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}