Filter Dates?

[Ken]

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

 

[bj]

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.

 

[Elkar]

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

 

[Ron de Bruin]

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

 

[Gord Dibben]

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