Ordinal Numbers in Dates

OfficeBitty · #1 11-20-2006, 11:37 AM

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 · #2 11-20-2006, 11:46 AM

Quote:

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 · #3 11-20-2006, 11:54 AM

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 · #4 11-20-2006, 12: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 · #5 11-20-2006, 12:04 PM

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

OfficeBitty · #6 11-20-2006, 12: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 · #7 11-20-2006, 12: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"))

OfficeBitty · #8 11-20-2006, 12: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 · #9 11-20-2006, 12:25 PM

Hi,

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

HTH
Carim

VBA Noob · #10 11-20-2006, 12:58 PM

Glad you got your answer

VBA Noob

OfficeBitty · #11 11-20-2006, 01: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.