Ask Your Question

Month and day with ordinals

asked 2020-11-06 04:15:38 +0100

DeafGamer2015 gravatar image

Hi there, I was trying to figure out how to do a code that input Month and Day for LibreOffice Calc. I tried various methods that I found on this site but they came back with errors.

I want to do this way.

EG: November 5th

I even used this but it's not the correct format code that I wanted.

=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 dd")

It came back as 5th November 5

How can I solve this?

Thank you.

edit retag flag offensive close merge delete


It looks as though you specified the date again at the end of your formula. Try this, I just deleted the dd to get=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").

Add yyyy to get year , e.g. 5th November 2020

Earnest Al gravatar imageEarnest Al ( 2020-11-06 04:28:25 +0100 )edit

Whoops, I just re-read, you want

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

for November 5th

I just changed comment because code broke in the middle.

Earnest Al gravatar imageEarnest Al ( 2020-11-06 04:32:17 +0100 )edit

Will give that a try. Thank you!

DeafGamer2015 gravatar imageDeafGamer2015 ( 2020-11-06 04:49:03 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-11-06 16:48:08 +0100

Opaque gravatar image

updated 2020-11-06 17:03:39 +0100


format your date using Format Code [NatNum12 D=ordinal-number]MMMM D
(of course something like =TEXT(TODAY();"[NatNum12 D=ordinal-number]MMMM D") works as well, but it would turn the date into text).

image description

Ref.: LibreOffice Help - Number Format Codes

Hope that helps.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-11-06 04:15:38 +0100

Seen: 43 times

Last updated: Nov 06 '20