Calculating Date of next birthday
 

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

Calculating Date of next birthday
 

On 22/12/08 2:52 PM, in article , "Ken
Lumley" wrote:

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

Calculating Date of next birthday
 

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(F 4),DAY(F4))-30
--
David Biddulph

"Ken Lumley" wrote in message
...
On 22/12/08 2:52 PM, in article , "Ken
Lumley" wrote:

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

calculate the age from 2 dates
 

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

Previous Posts In This Thread:

On Sunday, December 21, 2008 11:52 PM
Ken Lumley wrote:

Calculating Date of next birthday
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

On Monday, December 22, 2008 12:12 AM
Ken Lumley wrote:

Calculating Date of next birthday
On 22/12/08 2:52 PM, in article , "Ken
Lumley" wrote:

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

On Monday, December 22, 2008 1:33 AM
David Biddulph wrote:

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(F 4),DAY(F4))-30
--
David Biddulph

"Ken Lumley" wrote in message
...

EggHeadCafe - Software Developer Portal of Choice
RemoteSoft Decompiler,Obfuscator, Protector
http://www.eggheadcafe.com/tutorials...mpilerobf.aspx

calculate the age from 2 dates
 

=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)

<s. suganthi wrote in message ...
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 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

Previous Posts In This Thread:

On Sunday, December 21, 2008 11:52 PM
Ken Lumley wrote:

Calculating Date of next birthday
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

On Monday, December 22, 2008 12:12 AM
Ken Lumley wrote:

Calculating Date of next birthday
On 22/12/08 2:52 PM, in article , "Ken
Lumley" wrote:

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

On Monday, December 22, 2008 1:33 AM
David Biddulph wrote:

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(F 4),DAY(F4))-30
--
David Biddulph

"Ken Lumley" wrote in message
...

EggHeadCafe - Software Developer Portal of Choice
RemoteSoft Decompiler,Obfuscator, Protector
http://www.eggheadcafe.com/tutorials...mpilerobf.aspx

Calculating Date of next birthday
 

I went a slightly different route.

This is based on putting the persons date of birth i.e. 1/1/1980 in B4 and depending on todays date working out if the birthday is this year or next and adding the appropriate year


=IF(DATEVALUE((DAY(B4)&"/"&MONTH(B4)&"/"&YEAR(TODAY())))TODAY(),DAY(B4)&"/"&MONTH(B4)&"/"& (YEAR(TODAY())),DAY(B4)&"/"&MONTH(B4)&"/"&YEAR(TODAY())+1)

If it works for you then grand.

Calculating Date of next birthday
 

rolwey.stats wrote:

I went a slightly different route.

Okay, but... the original post was 8 years ago.

--
Goddammit grad school, you're making me overanalyze funny pictures of cats.

Nhanh tay chá»�n cho gia đình những vị trÃ* căn đẹp trong án, nhÃ* đã hoÃ*n thÃ*nh chuẩn bị trao nhÃ*. Căn há»™ Happy City cháy hÃ*ng trong lần mở bán đầu tiên.

Calculating Date of next birthday
 

On Monday, 22 December 2008 08:52:48 UTC+4, Ken Lumley wrote:
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

=EDATE(A1,CEILING.MATH(YEARFRAC(A1,TODAY()),1)*12)-30

where A1 is the birthdate