CODEWITHSUNDEEP
excelfiltering
Razick
Razick

Reputation: 814

Problems with Date Filtering in Excel

I am running an Advanced Filter on an excel data set from FRED. I've been trying to filter the data to include only January, April, July and October data (first month of each quarter). If I run an advanced filter with the criteria as a specific date (ex. 1/1/1995), the result returns as expected. However, as soon as I use any special search characters, (ex. 1/, 1//*, 1995 or 1/1/????) I get no results. I've read the excel documentation, and searched the internet for solutions but no one seems to be having this issue.

Ideally what I want to do is search for all of those months while excluding everything else (something like 1* OR 2* OR 3* or 4*) the first thing I need to figure out is why I can't seem to make filtering work period.

I'm guessing that the issue has something to do with how excel treats dates. Originally the dates were in the format DD-MM-YYYY but I realized that Excel was processing them as if they were in M-D-YYYY format so I changed the data to reflect that. I tried transforming the dates into regular text to get around that but Excel converted them to numbers that looked like "4/1/1995."

Keep in mind I am new to filtering so I'm probably making a rookie mistake.

Sample of Data and Criteria (Filter not applied)

observation_date    FEDFUNDS
1*  






observation_date    FEDFUNDS
1/1/1995            5.53
2/1/1995            5.92
3/1/1995            5.98
4/1/1995            6.05
5/1/1995            6.01
6/1/1995            6.00
7/1/1995            5.85
8/1/1995            5.74

Upvotes: 1

Views: 125

Answers (2)

jeffreyweir
jeffreyweir

Reputation: 4824

Wildcards only work on strings. Dates are not strings, but are numbers. So you need to use greater than and less than to set bounds in which to return numbers, as I have done below for the month of February. (Note that my dates are in non-US format)

enter image description here

Upvotes: 2

Angel
Angel

Reputation: 90

Here is a picture showing a working example with the filtering criteria followed by the filtered data and the advanced filter window with the ranges used in my case. From A2 to A5 is the dates I want the advanced filter to work on.

enter image description here

Filtering by part of a date (the way you see it in the cell) would not be possible with FILTER as far as I know.

Your feedback/ comment is appreciated!

Upvotes: 1

Related Questions