Formula to indicate upcoming birthdays

[Victor Delta]

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

 

[Rick Rothstein \(MVP - VB\)]

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?

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

 

[Ron Coderre]

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

 

[Victor Delta]

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.

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

 

[Rick Rothstein \(MVP - VB\)]

If you store the birthdays as text, say, like this... Mar 8, July 23,

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!).

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

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

Rick

 

[Victor Delta]

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