Problems filtering

[rubbermanjh]

I'm having problems with a number of worksheets that I've built up ove
years. They're fairly simple lists for my video and audio collection
plus others detailing events, TV shows, recording sessions, concer
dates, etc. Each is a separate worksheet. All have a date and artis
column in common. Each sheet has between 5 and 7,000 rows. I'm usin
Excel:mac 2001 on a couple of machines and Excel:mac v.X on another.

I've been trying to filter each list to tell me what happened on a se
day, let's say October 30th (a friend's birthday). I wanted to filte
for that date on any year and tried to follow what seeme
straight-forward enough guides. I clicked on (Custom Filter...) an
tried "begins with" and "contains" 30-Oct but got nothing on th
filtered list. Trying "equals" 30-Oct-?? or 30-Oct-* also resulted i
nothing. Trying "equals" 30-Oct-90 when I know there's an entry still
gives nothing. Strangely, a sheet with the dates in the format Oct 3
2000 in an "any value" column can be filtered perfectly.

Is there any way I can filter the lists I've got, or convert my date
to the format that seems to work. The sheet that can be filtered wa
recently copied from an old Amstrad PCW disk by way of a PC and I'm no
sure how I ended up with that date format.

One last qestion, if I can get this to work, is there any way that
can filter multiple sheets at once?

Thanks in advance,

John Hammon

 

[Bernie Deitrick]

John,

Dates are numbers in Excel, so the filtering is based on the number
and not the formatting. If you wanted, you could use three columns
for the dates- month, day, and year, or you could convert the dates to
formatted text strings using another column and the formula

=TEXT(A2,"mmmm d, yyyy")

which you could then use to filter using the Custom / Begins with
option.

HTH,
Bernie
MS Excel MVP