Filter Dates?
 

Excel2003 ... I have a Col containing Dates ... Format = Date 03/14/01

I selected subject Col & added a Filter Switch to it ... intent of Filter
Switch is to Filter Months by selecting "custom" then "begins with" ... then
enter applicable Month (01, 02, 03, 04, etc).

Issue is ... when I do this the Filtered data shows nothing???

Above said ... I have had this issue before ... so guidance would be welcome
....

Thanks ... Kha

Filter Dates?
 

the date may be formated as mm/dd/yy but it does not actually start with the
month
I suggest using a helper column with =month(A1) and use the autofilter on
this column.

"Ken" wrote:

Excel2003 ... I have a Col containing Dates ... Format = Date 03/14/01

I selected subject Col & added a Filter Switch to it ... intent of Filter
Switch is to Filter Months by selecting "custom" then "begins with" ... then
enter applicable Month (01, 02, 03, 04, etc).

Issue is ... when I do this the Filtered data shows nothing???

Above said ... I have had this issue before ... so guidance would be welcome
...

Thanks ... Kha

Filter Dates?
 

Excel stores dates as serial numbers. 03/14/01 is actually stored as 36964.
This represents the number of days since Jan 1, 1900. So, when you try to
filter by using "begins with", Excel looks at the serial number.

To get around this, there are a couple options. First off, you could store
your dates as text rather than dates. This would help with the filtering,
but may cause problems elsewhere if these dates are used in calculations.

Another option would be to insert a helper column. In this column, use a
formula like =MONTH(A1) to retrieve the month from the date stored in A1.
You can then apply the filter to this helper column instead.

HTH,
Elkar


"Ken" wrote:

Excel2003 ... I have a Col containing Dates ... Format = Date 03/14/01

I selected subject Col & added a Filter Switch to it ... intent of Filter
Switch is to Filter Months by selecting "custom" then "begins with" ... then
enter applicable Month (01, 02, 03, 04, etc).

Issue is ... when I do this the Filtered data shows nothing???

Above said ... I have had this issue before ... so guidance would be welcome
...

Thanks ... Kha

Filter Dates?
 

My EasyFilter add-in have a option to filter on months without a helper column
See
http://www.rondebruin.nl/easyfilter.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"bj" wrote in message ...
the date may be formated as mm/dd/yy but it does not actually start with the
month
I suggest using a helper column with =month(A1) and use the autofilter on
this column.

"Ken" wrote:

Excel2003 ... I have a Col containing Dates ... Format = Date 03/14/01

I selected subject Col & added a Filter Switch to it ... intent of Filter
Switch is to Filter Months by selecting "custom" then "begins with" ... then
enter applicable Month (01, 02, 03, 04, etc).

Issue is ... when I do this the Filtered data shows nothing???

Above said ... I have had this issue before ... so guidance would be welcome
...

Thanks ... Kha

Filter Dates?
 

Ken

Dates are serial numbers like 39133 which is formatted to look like 02/20/2007
and you can't filter them as you want by entering 01, 02, 03 etc.

See this google thread for a method by Max.

http://snipurl.com/1atea


Gord Dibben MS Excel MVP

On Tue, 20 Feb 2007 12:43:00 -0800, Ken wrote:

Excel2003 ... I have a Col containing Dates ... Format = Date 03/14/01

I selected subject Col & added a Filter Switch to it ... intent of Filter
Switch is to Filter Months by selecting "custom" then "begins with" ... then
enter applicable Month (01, 02, 03, 04, etc).

Issue is ... when I do this the Filtered data shows nothing???

Above said ... I have had this issue before ... so guidance would be welcome
...

Thanks ... Kha