Today I was surprised to find that Microsoft Excel 2010 still doesn’t have a format option for Ordinal Numbers; dates or numbers such as 1st, 2nd, 3rd, 4th (etc).  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 in-cell so I whipped up the following formula…

=TEXT(A2,"ddd, mmm d")  &
IF(OR(DAY(A2)=1,DAY(A2)=21,DAY(A2)=31),"st",
IF(OR(DAY(A2)=2,DAY(A2)=22),"nd",
IF(OR(DAY(A2)=3,DAY(A2)=23),"rd","th")))

It seemed like it should have been easier until I realized that 11,12,13 are th’s. The first line of this formula does nearly all of the work…the TEXT function can give Sat, Jun 4. 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.

You may find it easier to read if you use carriage returns in lengthy formulas.  Simply press ALT+ENTER while editing in a cell.  Also you can drag the formula box taller to ease the process. For more details on Excel’s TEXT function, look here http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx to see the formatting options and examples.  Expand the headers in the syntax section to see details about numbers, dates and times, currency, strings, etc. that TEXT display.

Let me know if you’ve found another way to do this.

Hey here are some, and they’re not limited to calendar days: http://www.cpearson.com/excel/ordinal.htm

---

Posted by Greg on June 5th, 2011 :: Filed under MS Excel,Tips
You can leave a response, or trackback from your own site.

---