Ask Your Question

How to get date ordinals in date format

asked 2017-10-05 12:42:50 +0200

EoghanM gravatar image

I'm using the following date format:


To display today's date: 5 October 2017

I can do the following to add the ordinal:


5th October 2017

But obviously this won't work correctly for e.g. 1st October 2017

Is there a way?

edit retag flag offensive close merge delete


No, I hope.
However, if there is one it will heavily depend on the locale, and as far as I can tell the many 'English (somehing)' locales may act differently. In the UK itself it is uncommon to use an ordinal mark in written dates. There may be thousands of excessively stubborn ways to write dates in the English speaking world.
Your D\th MMMM YYYY does not work for me with UI=locale=DocLanguage= English (UK). The h is taken for "hour".

Lupp gravatar imageLupp ( 2017-10-05 15:29:51 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-10-05 19:36:12 +0200

Jim K gravatar image

updated 2017-10-05 19:39:12 +0200

Adapted from

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

See also @Lupp's answer at

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-10-05 12:42:50 +0200

Seen: 402 times

Last updated: Oct 05 '17