Filter based on Date

[Abdul]

Hi,

Always I face a problem when i try to filter a range based on date. If
I do it manually it works fine. But when I try to do this thru code it
dont work.

May be there is a better way to do this?

when I recoded my action i get:

Sheets("Detail").select
Range("A1").AutoFilter Field:=1, Criteria1:=">=01/07/2006",
Operator:=xlAnd _
, Criteria2:="<=31/07/2006"

and when i run this code again no records are shown !!!

 

[Bob Phillips]

Damn US dates <g>

Dim date1_value As Date
Dim date2_value As Date

Sheets("Detail").Select
date1_value = DateValue("01-Jul-2006")
date2_value = DateValue("31-Jul-2006")
Range("A1").AutoFilter Field:=1, _
Criteria1:=">=" & Str(CDbl(date1_value)), _
Operator:=xlAnd, _
Criteria2:="<=" & Str(CDbl(date2_value))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Peter T]

Just wondering, is it necessary to do all that or simply

Range("A1").AutoFilter Field:=1, _
Criteria1:=">=07/Jul/2006", _
Operator:=xlAnd _
, Criteria2:="<=31/Aug/2006"

as long as the month is spelt and in US date order

Regards,
Peter T

 

[Bob Phillips]

Hi Peter,

My suggestion was more of a generic solution. It should work wherever you
get the date from, a cell, a userform etc., and filter correctly. You have
effectively shortcut my solution for those particular dates.

Regards

Bob

 

[Peter T]

Hi Bob,

I have always found that providing a date string is un-ambiguous it
correctly converts. Not sure about the "-" as date separator in all systems
(but "/" is always OK), also not sure if "July" is recognized in all
languages. But I see your solution would be better practice, I'll try not
to be lazy <g>

Regards,
Peter T

 

[Bob Phillips]

Peter,

I absolutely agree about unambiguous dates. When I use is SUMPRODUCT
solutions I try to use an ISO standard yyyy-mm-dd, I haven't yet heard from
anyone that "-" is a problem. And certainly July would not be recognised in
Excel with our friends across the channel.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Peter T]

And certainly July would not be recognised in

Excel with our friends across the channel.

'Le Weekend' seems to be understood well enough, perhaps it's just a matter
of time...

Regards,
Peter T

 

[Bob Phillips]

Maybe you should suggest that to President Chirac ... I'll stand back.

Regards

Bob

 

[Peter T]

A while back when he was a mayor I did quip something along those lines to
him. But even given in the best langue diplomatique I could muster I got the
impression it wasn't entirely appreciated.

Regards,
Peter T