Calculating Date of next birthday

[Ken Lumley]

Perhaps a strange type of request however I have a list of 500 birthdates
and I need to calculate 30 days before the next birthday for each and
display that as a date. I'm using excel 2003 for windows and excel 2004 for
mac.

I can calculate the age as at the next birthday in years and of course the
current age. What I can't seem to figure out how to do is calculate the date
of the next birthday.

Any help would be most appreciated.

Thanks

 

[Ken Lumley]

Perhaps a strange type of request however I have a list of 500 birthdates
and I need to calculate 30 days before the next birthday for each and
display that as a date. I'm using excel 2003 for windows and excel 2004 for
mac.

I can calculate the age as at the next birthday in years and of course the
current age. What I can't seem to figure out how to do is calculate the date
of the next birthday.

Any help would be most appreciated.

Thanks

Sorry all, I had a blank moment. The answer is relatively simple for anyone
who may be interested:

=((DATEDIF(F4,TODAY(),"y")+1)*365.25)+F4-30 where F4 contains the date of
birth

 

[David Biddulph]

I think you'll find that you'll often be a day out with that formula. As an
example, try it out with 1st Jan of various years as the DoB (and you may
want to remove the -30 term for the test to make it clearer).

Try =DATE(YEAR(F4)+(DATEDIF(F4,TODAY(),"y")+1),MONTH(F4),DAY(F4))-30

 

[s. suganthi]

calcuate the age from 2 different dates



Ken Lumley wrote:

Calculating Date of next birthday
21-Dec-08

Perhaps a strange type of request however I have a list of 500 birthdate
and I need to calculate 30 days before the next birthday for each an
display that as a date. I'm using excel 2003 for windows and excel 2004 fo
mac.

I can calculate the age as at the next birthday in years and of course th
current age. What I can't seem to figure out how to do is calculate the dat
of the next birthday

Any help would be most appreciated

Thanks

Previous Posts In This Thread:

Calculating Date of next birthday
Perhaps a strange type of request however I have a list of 500 birthdate
and I need to calculate 30 days before the next birthday for each an
display that as a date. I'm using excel 2003 for windows and excel 2004 fo
mac.

I can calculate the age as at the next birthday in years and of course th
current age. What I can't seem to figure out how to do is calculate the dat
of the next birthday

Any help would be most appreciated

Thanks

Re: Calculating Date of next birthday
On 22/12/08 2:52 PM, in article C5755A40.4CAE%[email protected], "Ke

Sorry all, I had a blank moment. The answer is relatively simple for anyon
who may be interested

=((DATEDIF(F4,TODAY(),"y")+1)*365.25)+F4-30 where F4 contains the date o
birth

I think you'll find that you'll often be a day out with that formula.
I think you'll find that you'll often be a day out with that formula. As an
example, try it out with 1st Jan of various years as the DoB (and you may
want to remove the -30 term for the test to make it clearer)

Try =DATE(YEAR(F4)+(DATEDIF(F4,TODAY(),"y")+1),MONTH(F4),DAY(F4))-3
-
David Biddulp


EggHeadCafe - Software Developer Portal of Choice
RemoteSoft Decompiler,Obfuscator, Protector
http://www.eggheadcafe.com/tutorial...0b-1cd879a04420/remotesoft-decompilerobf.aspx

 

[Bob Phillips]

=IF(DATE(YEAR(A2),MONTH(A2),DAY(A2))>TODAY(),
DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)),
DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)))-30


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)