Filter based on Date
 

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 !!!

Filter based on Date
 

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)

"Abdul" wrote in message
oups.com...
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 !!!

Filter based on Date
 

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" wrote in message
...
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)

"Abdul" wrote in message
oups.com...
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 !!!

Filter based on Date
 

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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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)

"Abdul" wrote in message
oups.com...
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 !!!

Filter based on Date
 

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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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)

"Abdul" wrote in message
oups.com...
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 !!!

Filter based on Date
 

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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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)

"Abdul" wrote in message
oups.com...
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 !!!

Filter based on Date
 

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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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)

"Abdul" wrote in message
oups.com...
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 !!!

Filter based on Date
 

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

Regards

Bob

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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)

Filter based on Date
 

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


"Bob Phillips" wrote in message
...
Maybe you should suggest that to President Chirac ... I'll stand back.

Regards

Bob

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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)