Next occuance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frustrated User - 21 Sep 2007 05:56 GMT

I am trying to create a spreadsheet list of client birthdays. I need a col
titled Date of Birth (this is obviously just data entry). The next col
needs to be Next Birthday and then one for age. I have the calculation
formula done for the age col. Is there an easy way to fill-in the values
for the "Next Bday" col?

Reply to this Message

Roger Govier - 21 Sep 2007 08:52 GMT

Hi

One way
=IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY(),
DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1)),
DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)))

Signature

Regards
Roger Govier

>I am trying to create a spreadsheet list of client birthdays. I need a col
> titled Date of Birth (this is obviously just data entry). The next col
> needs to be Next Birthday and then one for age. I have the calculation
> formula done for the age col. Is there an easy way to fill-in the values
> for the "Next Bday" col?

Reply to this Message

Ron Coderre - 21 Sep 2007 12:31 GMT

Perhaps this:
=IF(--TEXT(A1,"dd-mmm")<=TODAY(),--TEXT(A1,"dd-mmm"),--(TEXT(A1,"dd-mmm-")&(YEAR(TODAY())+1)))

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Reply to this Message

Ron Coderre - 21 Sep 2007 13:01 GMT

Well...THAT formula was wrong!

Try this:
=TODAY()+LOOKUP(365,--(TEXT(A1,"dd-mmm-")&(YEAR(NOW())+{0,1}))-TODAY())

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> Perhaps this:
> =IF(--TEXT(A1,"dd-mmm")<=TODAY(),--TEXT(A1,"dd-mmm"),--(TEXT(A1,"dd-mmm-")&(YEAR(TODAY())+1)))
[quoted text clipped - 13 lines]
>> formula done for the age col. Is there an easy way to fill-in the values
>> for the "Next Bday" col?

Reply to this Message

Ron Rosenfeld - 21 Sep 2007 13:24 GMT

>I am trying to create a spreadsheet list of client birthdays. I need a col
>titled Date of Birth (this is obviously just data entry). The next col
>needs to be Next Birthday and then one for age. I have the calculation
>formula done for the age col. Is there an easy way to fill-in the values
>for the "Next Bday" col?

=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(DOB),
DAY(DOB))<=TODAY()),MONTH(DOB),DAY(DOB))

--ron

Reply to this Message