asked on

Filter on Date and time

Hello,

I have slightly tricky filter problem in Excel 2007.

I have a filter which filters on the previous days date. For example, if today is 02/09/2010, the filter would show the data for 01/09/2010. Unfortunately, the data imported have a time stamp included with the date.

I would like a filter which would filter from midnight at the start of the previous day until the end of the previous day.

If the following filter was applied:
ActiveSheet.Range("$A$1:$H$5000").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=Date - 1, Criteria2:=Time 00:00<=23:59

the data displayed would be:

01/09/2010 00:03
01/09/2010 19:00
01/09/2010 23:43

Thanks in advance.

tilsant

Try this:

ActiveSheet.Range("$A$1:$H$5000").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, Date - 1)

Tils

peter57r

ActiveSheet.Range("$A$1:$H$5000).AutoFilter Field:=1, Operator:=xlAnd, Criteria1:=">=" & Format(Date - 1, "yyyy-mm-dd"), Criteria2:="<" & Format(Date, "yyyy-mm-dd")
End Sub

Sonia Bowditch

ASKER

Sorry guys,

I couldn't get either to work.

Tilsant, your solution did not show any records when the filter was applied.

peter57r, your solution had a compile error. The message was "Expected: list seperator or )" on "yyyy-mm-dd".

Thanks

tilsant

Can u post a sample data file?
Is your data having Column A with date values?
Is there any date with yesterday's date??

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sonia Bowditch

ASKER

Hello Tilsant,

Column A has data with date and time values and yes there is data with yesterday's date. The following is contained in column A:

27/08/2010 07:02
27/08/2010 07:02
27/08/2010 07:02
27/08/2010 07:02
27/08/2010 07:02
27/08/2010 07:02
27/08/2010 07:02
27/08/2010 07:02
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:03
27/08/2010 07:04
27/08/2010 07:04
27/08/2010 07:04
27/08/2010 07:04
27/08/2010 07:04
27/08/2010 07:04
27/08/2010 07:04
27/08/2010 07:04
27/08/2010 07:04
31/08/2010 12:42
31/08/2010 12:42
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33
01/09/2010 09:33

Thanks.

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tilsant

Try this one!

ActiveSheet.Range("$A$1:$H$5000").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, Format((Date - 1), "dd/mm/yyyy"))

Tils

Sonia Bowditch

ASKER

Thanks guys.

matthewspatrick, your first solution worked perfectly but your second returned no results.

peter57r, your solution worked perfectly and I have incorporated it into my macro.

Thanks for everyone's help.

Sonia Bowditch

ASKER

Sorry tilsant, I didn't spot your solution until I had closed the question.

tilsant

Did my last solution work for you??

I guess there was some conflict with the Date Format in VBA and Excel!

tilsant

And that might be the reason why matthewspatrick's 2nd solution didn't work for you!

Sonia Bowditch

ASKER

Hello tilsant,

Unfortunately your solution did not return any records. I agree with your assessment regarding a possible conflict.

Thank you very much for your time and effort.

tilsant

Thats ok infosec36. Points aren't a big concern here for me!

Tils

Patrick Matthews

Almost certainly a formatting conflict :)