1st, 2nd, 3rd, on Date Format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael - 23 Oct 2005 19:22 GMT

Looking to create a custom number format in a cell so that when I put the
date it updates it with the relevent st, nd, rd or th on the end of the date
entered

So that when I enter the
01/10/2005
it would display
1st October 2005

when I enter the
22/10/2005
22nd October 2005

i.e.
st, on the 1, 21, 31;
nd on 2, 22;
rd on the 23
and th on all the remainder of the dates within Excel date display entered
from the cell format

TIA
Michael

Reply to this Message

Anne Troy - 23 Oct 2005 21:54 GMT

The Ordinals man (among other things!):
http://www.cpearson.com/excel/ordinal.htm
************
Anne Troy
www.OfficeArticles.com

> Looking to create a custom number format in a cell so that when I put the
> date it updates it with the relevent st, nd, rd or th on the end of the
[quoted text clipped - 18 lines]
> TIA
> Michael

Reply to this Message

Gord Dibben - 23 Oct 2005 22:34 GMT

Also have a look at this google search thread for ordinal dates and methods of
returning.

Cannot be done through formatting alone. Either formula in a helper cell or
use Ron Rosenfeld's event code.

http://snipurl.com/iwlr

Gord Dibben Excel MVP

>The Ordinals man (among other things!):
>http://www.cpearson.com/excel/ordinal.htm
[quoted text clipped - 24 lines]
>> TIA
>> Michael

Reply to this Message