European/American Date Problem

[Nikki]

D/All
Firstly can I apologise in advance as I know this is a long protracted
subject as I have read loads of postings however I haven't found the
answer... and hope that there is one!

I'm basically asking the user to input two dates (dd/mm/yyyy) via two
separate InputBox commands to autofilter a report from a start date to
an end date. The column being filtered has dates in the format
dd/mm/yyyy.

If the user inputs a date that cannot be misread as American eg
31/09/2003 then the filter keeps the correct date but if the date is
input as 12/10/2003 the filter sees it as 10/12/2003.

I'm using English regional settings with short dates of dd/mm/yyyy
however this macro needs to work on other PCs which may not
necessarily have the correct setting.

How do I get the InputBox variable (dd/mm/yyyy) to stay the same in
the autofilter?

[XL2K, Win2K]

Many thanks
Nikki

 

[Tom Ogilvy]

Dim dtVal as Date
sStr = Application.Inputbox("Enter Date")
dtVal = cdate(sStr)

Selection.Autofilter Field:=1, Criteria1:=">=" & format(dtVal,"dd/mm/yyyy")

if that doesn't work, try using the date serial directly

Selection.Autofilter Field:=1, Criteria1:=">=" & clng(date)

cdate will use your regional settings to interpret the date string. If
someone else has different regional settings, it will use those.

 

[Nikki]

Tom

Unfortunately the first option of using cdate didn't seem to work.
Looking at the autofilter after it had taken the date in showed that
it would change 01/04/2003 (1st April) to 04/01/2003 (4th January).
However, the second option worked by using the following...

Selection.AutoFilter Field:=1, Criteria1:=">=" &
Format(CLng(startdate)), _
Operator:=xlAnd, Criteria2:="<=" & Format(CLng(enddate))

Thanks again for your help Tom.
Regards
Nikki
========

Dim dtVal as Date
sStr = Application.Inputbox("Enter Date")
dtVal = cdate(sStr)

Selection.Autofilter Field:=1, Criteria1:=">=" & format(dtVal,"dd/mm/yyyy")

if that doesn't work, try using the date serial directly

Selection.Autofilter Field:=1, Criteria1:=">=" & clng(date)

cdate will use your regional settings to interpret the date string. If
someone else has different regional settings, it will use those.

--
Regards,
Tom Ogilvy

D/All
Firstly can I apologise in advance as I know this is a long protracted
subject as I have read loads of postings however I haven't found the
answer... and hope that there is one!

I'm basically asking the user to input two dates (dd/mm/yyyy) via two
separate InputBox commands to autofilter a report from a start date to
an end date. The column being filtered has dates in the format
dd/mm/yyyy.

If the user inputs a date that cannot be misread as American eg
31/09/2003 then the filter keeps the correct date but if the date is
input as 12/10/2003 the filter sees it as 10/12/2003.

I'm using English regional settings with short dates of dd/mm/yyyy
however this macro needs to work on other PCs which may not
necessarily have the correct setting.

How do I get the InputBox variable (dd/mm/yyyy) to stay the same in
the autofilter?

[XL2K, Win2K]

Many thanks
Nikki