excel date format problem

[Tim]

Dear All,

i'm trying to set a custom autofilter using VBA/inputboxes. everything
seems to work BUT I am trying to filter dates taken from inputboxes and in
the process, the days and months are being transposed... eg, in the
inputbox, i entered '1/6' meaning '1st June 2008' but the
autofilter is showing '6th January 2008'... obviously returning the wrong
result.

any ideas how to fix that?! I'm using the following syntax to set & use the
date variable: -
==
Dim rng1, rng2
rng1 = Format(CDate(InputBox("from")), "dd/mm/yyyy")
rng2 = Format(CDate(InputBox("to")), "dd/mm/yyyy")
Selection.AutoFilter Field:=3, Criteria1:="<=" & rng1, Operator:=xlAnd,
Criteria2:=">=" & rng2
===

tia,

Tim

 

[Bob Phillips]

Try this

Dim rng1, rng2
With Selection
rng1 = Format(CDate(InputBox("from")), .Cells(1,1).Numberformat)
rng2 = Format(CDate(InputBox("to")), .Cells(1,1).Numberformat)
.AutoFilter Field:=3, Criteria1:="<=" & rng1,
Operator:=xlAnd,Criteria2:=">=" & rng2
End With