Autofilter on date macro not behaving as I think it should.

[JonathanA]

Hi there,

This has really foxed me, I have part of a macro that filters a table in Excel 2007, to only show the relevant data for that week, last week it was working fine but now it has decided to start showing 2 weeks of data even though the filter specifies just one date. Here is a snippet of my code.

Sub Macro15()

        Sheets("Report").Select
        
    mydate = Range("B2").Value
    'This is a date in cell formatted as UK date dd/mm/yyyy or 11/03/2011
    
    mydatestring = Format(mydate, "mm/dd/yyyy")
Sheets("Data").Select
        ActiveSheet.ListObjects( _
        "Table_View_Sales_Target_Report").Range. _
        AutoFilter Field:=1
        ActiveSheet.ListObjects( _
        "Table_View_Sales_Target_Report").Range. _
        AutoFilter Field:=2
        ActiveSheet.ListObjects( _
        "Table_View_Sales_Target_Report").Range. _
        AutoFilter Field:=1, Criteria1:="Jon A"
        ActiveSheet.ListObjects( _
        "Table_View_Sales_Target_Report").Range.AutoFilter _
        Field:=2, Operator:=xlFilterValues, Criteria2:=Array(1, mydatestring)
End Sub

This code now leaves the table filtered on 11/03/2011 and 04/03/2011, 11th and 4th March whereas last week it worked fine filtering on 04/03/2011 (it is supposed to be a couple of weeks behind) I could understand the problem if the UK and US dates could be misinterpreted, i.e if there was a date in the data of 03/11/2011. I have checked the serial numbers of the 2 dates in question and they are 40606 (04/03/2011) and 40613 (11/03/2011).
Please can anybody help this has really flummoxed me.

 

[John_w]

Maybe http://www.ozgrid.com/VBA/autofilter-vba-dates.htm applies.

 

[JonathanA]

Thanks for that, works perfectly now.
Just very annoying about US dates, problems always cropping up everywhere.
Who on earth thinks that MM/DD/YYYY is more logical or makes more sense than DD/MM/YYYY, rant over!
Thanks again.