How can I filter for Birthdays/Anniversaries in Excell?

W

Woodkat

I have a column of birthdays and would like to be able to filter only those
birthdays that occur in the next 14-21 days so we can mail cards. Is there a
way to do this?
 
F

Frank Kabel

Hi
use a helper column with the following formula in it:
=IF(AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>=TODAY()+14,DATE(YEAR(TOD
AY()),MONTH(A1),DAY(A1))<=TODAY()+21,"X","")
and copy this for all rows
 
B

Bill Martin

I have a column of birthdays and would like to be able to filter only those
birthdays that occur in the next 14-21 days so we can mail cards. Is there a
way to do this?
Click to expand...

How about this. Assume the birthdays are in column A in date format such as
9/23/1950. Then create a second column B where

B1 = if(mod(days360(today(),A1),360) < 21,"Send Card!","")

Then just copy that all the way down the B column.

Good luck...

Bill -- (Remove KILLSPAM from my address to use it)
 
J

JulieD

Hi Woodkat

Something along the lines of
=IF(AND(DATE(2004,MID(A1,5,2),RIGHT(A1,2))>TODAY()+14,DATE(2004,MID(A1,5,2),RIGHT(A1,2))<TODAY()+21),"card","")

Cheers
JulieD
 
F

Frank Kabel

Hi
try to convert them first to a real Excel date. e.g. with the formula
=--TEXT(A1,"0000-00-00")
format this cell as date and copy for all rows. Now use this helper
column
 
Top