advanced filter macro not accepting date ranges

[krazyderek]

i had a macro for taking a database and applying an advanced filter to get info on an employee for each month with the following macro

Range("J14").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Database").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("D11:F12"), CopyToRange:=Range("A13:J13"), Unique:=False

where D11:D12 is the employee name criteria, and E11:F12 is the date range, everything worked fine in excel 2003, but it doesn't work in 2010. If i back out the criteria to just be D11:D12 it works, but then i don't have a specific date range....

here's the real twist, if i do everything manually by clearing the data, going to advanced filter, it works with the date range, but the macro for the exact same steps, won't, kinda stumped...
the macro is just a module, maybe it needs to be somewhere else for 2007/2010?

 

[Juan Pablo González]

If you change the date cell's format to General, does it work?

 

[krazyderek]

If you change the date cell's format to General, does it work?

no effect on the macro, if i do it manually by going and clicking through advanced filter it works with the date cells formatted as date or general

 

[Juan Pablo González]

F12 has a date ? or what does it have?

 

[krazyderek]

F12 has a date ? or what does it have?

sorry,
E11 and F11 both have the header "Date"
E12 has >31/12/2010
F12 has <1/2/2011

 

[Juan Pablo González]

Change E12 to

=">" &DATE(2010,12,31)

And F12 to

="<" &DATE(2011,2,1)

 

[krazyderek]

Change E12 to

=">" &DATE(2010,12,31)

And F12 to

="<" &DATE(2011,2,1)

that's great, the macro works now, but the question is why? the manual advanced filter was working, why would a macro need the date's formated in a formula like this?

 

[Juan Pablo González]

Regional settings basically. VBA understands date in the US format, mm/dd/yyyy, so working with dates is always a pain.

 

[krazyderek]

Change E12 to

=">" &DATE(2010,12,31)

And F12 to

="<" &DATE(2011,2,1)

another problem popped up, where in the example above, it will include items from 2010,12,31 and great in the data range instead of only items greater then 2010,12,31

is it possible to add time after the date and set it to > 2010,12,31 11:59:59pm ??

 

[Juan Pablo González]

another problem popped up, where in the example above, it will include items from 2010,12,31 and great in the data range instead of only items greater then 2010,12,31

is it possible to add time after the date and set it to > 2010,12,31 11:59:59pm ??

Try this

=">"&DATE(2010,12,31)+TIME(23,59,59)