Date range selection? Anniversary dates


G

Guest

I am trying to search a data set with several columns of dates and pull any
date within a range that I type in for the system to search. A small sample
is below.

I want to set it up so I can search once a month or so for people who are
hitting these anniversary milestones. I want to enter a range of say
07/01/1985-07/31/1985 and pull the name and what anniversary year (ie
1,5,10,15) for anyone in the given data set. Be advised that my actual data
set is much much larger.


HIRE DATE NAME OFFICE 1 YR 5 YR 10 YR 15 YR
10-Apr-79 SHARON RV 4/10/1980 4/10/1984 4/10/1989 4/10/1994
08-Jul-80 PAM RV 7/8/1981 7/8/1985 7/8/1990 7/8/1995
23-Feb-87 HARRIET RV 2/23/1988 2/23/1992 2/23/1997 2/23/2002
13-Jul-87 RON RV 7/13/1988 7/13/1992 7/13/1997 7/13/2002
16-Sep-87 TAMMIE RV 9/16/1988 9/16/1992 9/16/1997 9/16/2002
18-Apr-88 CINDY RV 4/18/1989 4/18/1993 4/18/1998 4/18/2003
23-Jan-89 MONICA RV 1/23/1990 1/23/1994 1/23/1999 1/23/2004
16-Jul-90 JAMES RV 7/16/1991 7/16/1995 7/16/2000 7/16/2005
 
Ad

Advertisements

P

Peo Sjoblom

Use autofilter, from dropdown select custom and greater than or equal to
07/01/1985,
AND
less than or equal to 07/31//1985
 
G

Guest

This does not help. I am giving this to people who will not remember how to
even use filter. I am trying to set up a function so they only have to enter
the dates in two cells on a workseet to get the answers.
 
G

Guest

Peo,

I am not giving you attitude. I am sure you are a very intelligent person
and I am sure that you want to help based on your fast response. It was
apprciated just not what I need.
 
P

Peo Sjoblom

OK, sorry if I was overreacting.
You could for instance automate the autofilter using VBA so basically all
the users have to do is to type in the 2 dates and press a button


The problem using formulas if indeed the data set is large is that it might
make the workbook slow down to a crawl, you would need fairly complicated
array formulas, something like number 6 from here

http://nwexcelsolutions.com/advanced_function_page.htm

=IF(ROWS(B$1:B1)<=COUNTIF(First!$A$1:$A$20,$A$1),INDEX(First!$B$1:$B$20,SMALL(IF(First!$A$1:$A$20=$A$1,
ROW(First!$A$1:$A$20)-ROW(First!$A$1)+1),ROWS(B$1:B1))),"")


Now since you want all information you would need to change the INDEX part
to reflect the different columns and use multiple formulas and the SMALL
part would change from (A1:A20>=date1)*(A1:A20<=date2)



--
Regards,

Peo Sjoblom
 
Ad

Advertisements

G

Guest

Peo,

Ok, now I feel retarded. I have not used VBA at all in since I was in
college in 2002 and can not remember how to use it. How eaxctly do I automate
the autofilter?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top