Ordinal Numbers in Dates

**OfficeBitty** · 11-20-2006, 12:37 PM

Hi everyone,

I've been trying to figure out ordinal numbers in Excel, and I found information on how to turn a cardinal number into an ordinal number... but now I need to figure out if there is a way to have them take the number from a date, and return it in a date format... Like this:

A1 = 11/25/06
B1 = 25th day of November, 2006

I would appreciate any advice/assistance.

**starguy** · 11-20-2006, 12:46 PM

Originally Posted by OfficeBitty

Hi everyone,

I've been trying to figure out ordinal numbers in Excel, and I found information on how to turn a cardinal number into an ordinal number... but now I need to figure out if there is a way to have them take the number from a date, and return it in a date format... Like this:

A1 = 11/25/06
B1 = 25th day of November, 2006

I would appreciate any advice/assistance.

it would be easy if you maintain a table like this

1 January
2 February
3 March
and so on...

then use this formula (I suppose that date is entered in cell A1 and table like above for 12 months is in the range of D1:E12)

=DAY(A1)&" "&"day of "&VLOOKUP(MONTH(A1),$D$1:$E$12,2,FALSE)&", "&YEAR(A1)

you can change ranges according to your data.

Regards.

**VBA Noob** · 11-20-2006, 12:54 PM

Hi,

or

=DAY(A1)&IF(INT(MOD(DAY(A1),100)/10)=1, "th", IF(MOD(DAY(A1),10)=1, "st", IF(MOD(DAY(A1),10)=2,"nd", IF(MOD(DAY(A1),10)=3, "rd","th"))))& " day of " & TEXT(A1,"mmmm yyyy")

VBA Noob

**starguy** · 11-20-2006, 01:03 PM

for having "rd", "nd" and "th" at the end of day number.
try this

IF(RIGHT(DAY(A1),1)*1=1,"1st",IF(RIGHT(DAY(A1),1)*1=2,"2nd",IF(RIGHT(DAY(A1),1)*1=3,"3rd",DAY(A1)&"t h")))&" day of "&VLOOKUP(MONTH(A1),$D$1:$E$12,2,FALSE)&", "&YEAR(A1)

**starguy** · 11-20-2006, 01:04 PM

VBA Noob's formula is good if you do not want to create table.

**OfficeBitty** · 11-20-2006, 01:06 PM

Thank you both for the advice, StarGuy & VBA Noob!

I was able to use the information you two provided to get the result that I was trying for!

**Carim** · 11-20-2006, 01:07 PM

Hi,

Along side VBA Noob's formula, you can try this shorter one ...(no table)

Code:

=A1&IF(AND(A1>=10,A1<=14),"th",CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

HTH
Carim

**OfficeBitty** · 11-20-2006, 01:15 PM

Thank you for your advice also, Carim!

I tried your formula (the no table part is always great!), but I think I might have done something wrong as the cell I put the formula into gets a thick border around it, and displays a long number... what could be causing this?

**Carim** · 11-20-2006, 01:25 PM

Hi,

It is related to your own cell settings ... just format cell as you wish ...

HTH
Carim

**VBA Noob** · 11-20-2006, 01:58 PM

Glad you got your answer

VBA Noob

**OfficeBitty** · 11-20-2006, 02:30 PM

I'm sorry Carim, I still can't seem to get your formula to work for me... I keep fiddling with my cell format, but can't get the result to change... I'll keep trying it, but in the meantime, your advice did get me thinking about using a shorter formula... I decided to use a function for making the ordinal number, and that way I could use parts of StarGuy's formula and also parts of VBA Noob's formula, and make it short like yours to get the same result.

=OrdinalNumber(DAY(A1))&" "&"day of "&TEXT(A1,"mmmm yyyy")

If you have any idea how I could be getting your formula wrong, Carim, please advise me a little further on that one. I've reformatted the cell and other cells and pasted your formula in different cells, but I can't seem to get it right.