Try this instead

=YEAR(B1)-YEAR(A1)&IF(OR(YEAR(B1)-YEAR(A1)={1,21,31,41,51,61,71,82,91}),"st"
,
IF(RIGHT(YEAR(B1)-YEAR(A1),1)="2","nd",IF(RIGHT(YEAR(B1)-YEAR(A1),1)="3","rd
","th")))

--
HTH

Bob Phillips

"TUNGANA KURMA RAJU" wrote in
message ...
A miillion thanks to Mr.Ron Rosenfeld.Its a great job you have
done.Actually
my question(5)is wrong,I want my upcoming birth day's(age) ordinal.All
other
formulas working fine.Iam analysing the logic of answer 6.What a great
logic.Thanks once again.

"Ron Rosenfeld" wrote:

On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU
wrote:

I am working on a personal worksheet.In cell A1 I put my Date of
birth(1-NOV-1975) in ddmmmyyyy format.What formula I need to write to
get(in
B1 to B6)
1.What date will be my next birth day?(output:1-NOV-2005)

"=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),
MONTH(A1),DAY(A1))<=TODAY()),MONTH(A1),DAY(A1))"

2.What day it will be?(output:Tuesday)

=TEXT(B1,"dddd")

3.How many days left for my upcoming birth day?(output:21 days)

=B1-TODAY()

4.Whether today is my birth day or not?(output:no)

=IF(B1=TODAY(),"Yes","No")

5.What will be my upcoming birth day's ordinal?(output:30th)

=DAY(B1)&IF(OR(DAY(B1)={1,21,31}),"st",
IF(OR(DAY(B1)={2,22}),"nd",IF(OR(
DAY(B1)={3,23}),"rd","th")))

6.Date of my earliest upcoming birth day that falls on sunday?(say
1-NOV-2008)

This is an array formula. After typing or pasting it in, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...}
around the
formula.

=DATE(YEAR(B1)+MATCH(TRUE,WEEKDAY(DATE(
YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(
A1),DAY(A1))<=TODAY())+ROW(INDIRECT(""1:10"")),
MONTH(A1),DAY(A1)))=1,0),MONTH(B1),DAY(B1))

For me I tried with date formulas.some gave output errors ,numbers.
office xp version,widows me.


--ron