date calculations

[TUNGANA KURMA RAJU]

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)
2.What day it will be?(output:Tuesday)
3.How many days left for my upcoming birth day?(output:21 days)
4.Whether today is my birth day or not?(output:no)
5.What will be my upcoming birth day's ordinal?(output:30th)
6.Date of my earliest upcoming birth day that falls on sunday?(say 1-NOV-2008)
For me I tried with date formulas.some gave output errors ,numbers.
office xp version,widows me.

[Ron Rosenfeld]

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

[Bob Phillips]

"TUNGANA KURMA RAJU" <[email protected]> wrote in
message news:[email protected]...
> 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)


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

=IF(B1=TODAY(),"","no")

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

[TUNGANA KURMA RAJU]

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
>

[Paul Sheppard]

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
>

Hi

To get the ordinal use this formula > =DATEDIF(A1,B1,"y"), you will need analysis pak on for this to work

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
> 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
> >

[Roger Govier]

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
>><[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
>>
>>
>>

[Bob Phillips]

Because it is his 30th birthday! :-)

Bob

"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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
> >><[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
> >>
> >>
> >>

[Roger Govier]

Hi Bob

Doh!!!
Upcoming age ordinal, which I read as Birthday's ordinal.
Glad the rest of you are awake, now where's that coffee pot????

Regards

Roger Govier



Bob Phillips wrote:

>Because it is his 30th birthday! :-)
>
>Bob
>
>"Roger Govier" <[email protected]> wrote in message
>news:[email protected]...
>
>
>>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
>>>><[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
>>>>
>>>>
>>>>
>>>>
>>>>
>
>
>
>

[Bob Phillips]

Hi Roger,

Don't know what you are worried about, I missed that part off completely in
my first response. Perhaps I need the whisky pot :-).

Bob

"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Doh!!!
> Upcoming age ordinal, which I read as Birthday's ordinal.
> Glad the rest of you are awake, now where's that coffee pot????
>
> Regards
>
> Roger Govier
>
>
>
> Bob Phillips wrote:
>
> >Because it is his 30th birthday! :-)
> >
> >Bob
> >
> >"Roger Govier" <[email protected]> wrote in message
> >news:[email protected]...
> >
> >
> >>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
> >>>><[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
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >
> >
> >
> >

[Ron Rosenfeld]

On Mon, 10 Oct 2005 23:56:03 -0700, TUNGANA KURMA RAJU
<[email protected]> 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.

You're welcome.

With regard to the ordinal, once you understand the logic of the answers, you
should be able to alter it to generate age ordinal's. Merely expand the OR
functions to include the appropriate numbers for each type of ordinal.


--ron

[Ron Rosenfeld]

On Tue, 11 Oct 2005 11:05:29 +0100, "Bob Phillips"
<[email protected]> wrote:

>Hi Roger,
>
>Don't know what you are worried about, I missed that part off completely in
>my first response. Perhaps I need the whisky pot :-).
>
>Bob

As did I!
--ron