Automatically add suffixes to ordinal numbers in OpenOffice or LibreOffice

A number of responses on the Oo.o/LO forums regarding the question of adding ordinal notation to dates have said that the only way to add the suffixes st, nd, rd, and th to dates is with macros.  I searched for the same question in the Excel [ahem] arena and quickly came up with a solution:

=DAY(A1)&IF(OR(DAY(A1)={1,2,3,21,22,23,31}),
CHOOSE(1*RIGHT(DAY(A1),1),"st","nd ","rd "),"th ")
&TEXT(A1,"mmmm, yyyy")

If you cut/paste, make sure to remove the carriage returns.  This ingenious bit of magic gives the date in this format: “30th June, 2001”.  Note that in the original post (at tips.net), there is a space missing after “th”; I have corrected that in my copying of his formula.

I needed a slightly different form to enter via a mail merge field into an official-sounding certificate: “30th day of June, 2001”.  After some trial and error, here is my solution:

=DAY($A$2)&IF(OR(DAY($A$2)={1,2,3,21,22,23,31}),CHOOSE(1*RIGHT(DAY($A$2),1),”st day “,”nd day “,”rd day “),”th day “)&TEXT($A$2,”of mmmm, yyyy”)

Obviously, adjust the cell reference to meet your needs.  In case you’re curious, trying to put ‘day’ into the final TEXT argument didn’t work because Calc read the ‘d’ as a date format, giving me “30th 30ay of June, 2001″.

 

Advertisements

About anisly

This is Yet Another Tech Blog--my attempt at propagating some of what I've gleaned in trying to solve tech problems. YMMV, but enjoy
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

One Response to Automatically add suffixes to ordinal numbers in OpenOffice or LibreOffice

  1. StuartC says:

    In LibreOffice 5.2.4.2 its under:

    Tools / AutoCorrect /AtuoCorrectOptions / Localized Options / Format ordinal number suffixes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s