Formula to indicate upcoming birthdays

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Victor Delta - 09 Jun 2007 17:42 GMT

Can someone please help me regarding a formula which will enable me to
highlight upcoming birthdays on a spreadsheet.

I have no problem creating a formula to highlight, say, a date which falls
in the next 30 days. However, in this case the dates in question are my
friends' dates of birth so the years in such fields are not this year.

Can anyone help please?

TIA

V

Reply to this Message

Rick Rothstein (MVP - VB) - 09 Jun 2007 18:06 GMT

> Can someone please help me regarding a formula which will enable me to
> highlight upcoming birthdays on a spreadsheet.
[quoted text clipped - 4 lines]
>
> Can anyone help please?

If you store the birthdays as text, say, like this... Mar 8, July 23,
November 13, etc., then you can form the current years birthday in a column
using this formula...

=--(B2&", "&YEAR(NOW()))

You can then apply your current highlighter formula to that date.

Rick

Reply to this Message

Victor Delta - 09 Jun 2007 19:31 GMT

> If you store the birthdays as text, say, like this... Mar 8, July 23,
> November 13, etc., then you can form the current years birthday in a
[quoted text clipped - 3 lines]
>
> You can then apply your current highlighter formula to that date.

Thanks for that suggestion.

Actually I like storing the dates as DOBs since then I also know which
birthday is being celebrated this year (in case it's a 'special' one!).

V

Reply to this Message

Rick Rothstein (MVP - VB) - 09 Jun 2007 19:49 GMT

>> If you store the birthdays as text, say, like this... Mar 8, July 23,
>> November 13, etc., then you can form the current years birthday in a
[quoted text clipped - 8 lines]
> Actually I like storing the dates as DOBs since then I also know which
> birthday is being celebrated this year (in case it's a 'special' one!).

If the birthday is in A1, then this years birthday date can be found with...

=DATE(YEAR(NOW()),MONTH(A1),DAY(A1))

Rick

Reply to this Message

Victor Delta - 09 Jun 2007 20:10 GMT

> If the birthday is in A1, then this years birthday date can be found
> with...
>
> =DATE(YEAR(NOW()),MONTH(A1),DAY(A1))

Many thanks,

V

Reply to this Message

Ron Coderre - 09 Jun 2007 18:39 GMT

With these values in A1:B5

Adam    6/15/1980
Bernice    9/12/1981
Carl    4/10/1979
Dana    6/19/1985
Eileen    7/10/1955

Try this:
Select A1:A5, with A1 as the active cell

From the Excel main menu:
<format><conditional formatting>
Condition_1:
• Formula is:
=AND(DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))<=TODAY()+30)

...Set the format you want .....

Click [OK] when done

In the above example, if today is 06/09/2007, these name would be highlighted:
Adam (06/15/1980)
Dana (06/19/1985)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

> Can someone please help me regarding a formula which will enable me to
> highlight upcoming birthdays on a spreadsheet.
[quoted text clipped - 8 lines]
>
> V

Reply to this Message