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
> <[email protected]> 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
>
Bookmarks