Finding records between two dates

  • Thread starter Thread starter Steve Faber
  • Start date Start date
S

Steve Faber

I have a table of data, and I have created a UserForm for
me to enter a start and finish date. These dates should
then be used as criteria for finding records between
those two dates (and including them: >=startdate and
<=enddate).

I've tried using Data/Filter/Autofiler and the custom
option to specify the "greater/less than or equal to"
options, but I have to pick the startdate from the list
of dates shown. I've tried creating a criteria range,
but can't find how to specify after the startdate AND
before the enddate - using more than one row creates an
OR condition.

Lots of people must do things like this - how can I??

Thanks

Steve
 
You don't have to pick a date from the list in autofilter
you can manually type in the dates..
 
Peo,

thanks for that.

I have put together a test macro (which will become part of the VB procedure
for my userform when it's working!) as follows

StartDat = Sheets("Expense Record").Range("N1").Value
sd = ">=" & StartDat
Selection.AutoFilter Field:=1, Criteria1:=sd, Operator:=xlAnd,
Criteria2:="<=15/09/2003"

I have a procedure which writes the start date from the userform into N1 on
that Expense Record sheet in the format dd/mm/yy. However, the macro above
(or Excel itself) is interpreting the date as mm/dd/yy - e.g. 9th June 2003,
rather than 6th September 2003. I think this is Excel (I'm using Excel 2000
SP-3), as although my regional settings for Windows are defined to use the
English format dates (dd/mm/yy for Short Date), when I choose
Format/Cells/Number/Date the date formats are still month first (mm/dd/yy).

So I think the problem is not my macro returning incorrect results, but it
returning results that I don't want because of the confused date format.

Any ideas?

Steve
 
Back
Top