European/American Date Problem

  • Thread starter Thread starter Nikki
  • Start date Start date
N

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
 
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.
 
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
========
Tom Ogilvy said:
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



Nikki said:
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
Click to expand...
Click to expand...
 
Back
Top