Extract data from a list within a specific date range

  • Thread starter Thread starter Jennifer Bremer
  • Start date Start date
J

Jennifer Bremer

I have a two part question.
Part One
I am trying to filter data within a list. My list
contains the following information:
LAST NAME FIRST NAME ADDRESS CITY STATE ZIP DOB

I want to be able to filter my list to display the above
information only for people whose birthday falls within a
specific mm/dd range. I don't care about the year.

IE. 11/1 - 11/8

Part Two
Once my data is filtered how do I create a mail merge to
print mailing labels.

My goal is to print mailing labels for people in the list
whose birthdays fall between a specific date ie. 11/1 -
11/8 so that I can mail them birthday cards.

Please help!
 
You can create two help columns next to the DOB, fisr one called Month
second DAy,
in the Month put this formula

=MONTH(G2)

copy down

=DAY(G2)

copy down

where G2 is the first cell with the DOB. Now use autofilter and filter on
Month 11
and custom on day with greater than or equal to 1 AND less than or equal to
8

Then you can copy this list with headers and then use mailmerge in word.
You can also filter from word while doing mailmerge..
 
Regarding Part One.

Let A5:G20 houses the data, with labels (LAST NAME,etc.) in A5:G5.

In A1 enter: 11/1 [ smallest criterion date ]
In A2 enter: 11/8 [ largest criterion date ]

Leave B1 empty.
In B2 enter:

=AND(MONTH(G6)>=MONTH($A$1),MONTH(G6)<=MONTH($A$2),DAY(G6)>=DAY($A$1),DAY(G6
)<=DAY($A$2))

Note that G6 is the first DOB value in the data area.

Select A5:G20.
Activate Data|Filter|Advanced Filter.
Choose an option for Action.
Enter $A$5:$G$5 in the box for List range.
Enter $B$1:$B$2 in the box for Criteria range.
Leave empty (or enter a cell address in) the box for Copy to.
Click OK.
 
Jennifer

Part One.........see Part Two

Part Two............Use the Mailmerge Wizard in Word to set your label type
and size. Have your Excel database as the source for the addresses. You will
be able to filter/query for your date range directly in Word.

Gord Dibben XL2002
 
You can add a column (BDay) to the table, and calculate each person's
birthday for the current year. For example, in cell H2, enter:

=DATE(YEAR(TODAY()),MONTH(G2),DAY(G2))

Copy the formula down to the last row of data.

To filter in Excel, choose Custom from the dropdown list in the BDay
column heading. Choose 'greater than or equal to' and enter your start
date, and 'less than or equal to' and enter your end date'

To create labels, it's easiest to use Microsoft Word. In Word, use the
Mail Merge wizard to set up the labels. When you want to print labels
for a specific range of dates, click the Query Options button in the
Mail Merge Wizard. Choose the BDay field, and set the range of dates.

There are Mail Merge instructions here:
http://www.mvps.org/word/FAQs/MailMerge/
 
Back
Top