Filltering data between two date ranges

D

Dave

Hi,

wonder if anyone can help me - I have a list containing peoples
birthdays. I wish to be able to enter a date range i.e. today and 2
weeks into the future. I then wish for the list to be filtered
showing only those birthdays within the date range. The date range
could span across 2 different months.

I'm not particularly skilled in using features such as VBA etc, so if
there is a simple solution it would be much appreciated.

Many thanks for any help.
 
B

Biff

Hi Dave!

If you enter the birthdays as Month/Day this is somewhat
easy. If you enter the birthdays as Month/Day/Year it is
much more difficult.

Here's a way to do it using the birthdays entered as
Month/Day.

Assume the persons name is in the range A5:A200 and the
Bday is in the range B5:B200 entered in the format:
Month/Day. The 2 date ranges are in C1 and D1. You want
the range of dates to be between today and 2 weeks from
today so, in C1 enter: =TODAY() In D1 enter: =C1+14

Now to extract the persons and their Bdays:

Say in D5 enter this formula and copy down until you get
#NUM! errors:

Entered as an array - CTRL,SHIFT,ENTER

=INDEX(A$5:A$200,SMALL(IF(B$5:B$200>=C$1,IF
(B$5:B$200<=D$1,ROW(INDIRECT("A$1:A$"&COUNTA
(A$5:A$200))))),ROW(1:1)))

That will extract the names.

Now, in E5 enter this formula and copy down as far as data
is in col D, or, just double click the fill handle:

=INDEX(B$5:B$200,MATCH(D5,A$5:A$200,0))

Format as Date - Month/Day

This will extract the persons Bday.

Biff
 
D

Debra Dalgleish

You could add a couple of columns to your table, and calculate the
upcoming birthdays. For example, if the birthdates start in cell B2:

Add the heading "This Year" to cell C1
In cell C2, enter: =DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))

Add the heading "Upcoming" to cell D1
In cell D2, enter: =AND(C2>=TODAY(),C2<=TODAY()+13)

Turn on the AutoFilter, and filter column D for TRUE
 
Top