Because it is his 30th birthday! :-)

Bob

"Roger Govier" wrote in message
...
Hi

The ordinal of every Birthday will be the same, so your upcoming
Birthday's ordinal is the same as your date of birth.
You gave that in your original posting as 01-NOV-1975 and Ron's formula
will rightly give 1st as the result.
In your posting, you seemed to expect 30th as the result, but I cannot
see why.

Regards

Roger Govier



TUNGANA KURMA RAJU wrote:

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