matching dates from imported *.dbf file?

[jeffP]

HI all,
I have a simple WS of 3 columns date,custnum,invnum that has dates over past
5 years. I simply want to have user input the date desired and get
corresponding data to another WS. I'm sure there are many ways to do this
and I thought I'd try working w/ Advanced filter.
My problem is that I can't get my user input date to match up with anything
in the imported database date. I tried all kinds of formatting and even
tried copying a cell from the import data and paste it into the user data
cell. It won't match up even with itself!

Could you explain to me what I am missing?

 

[Tom Ogilvy]

Sounds like your dates from the dbf are being interpreted as Text. assume
the first date is in A2. In any cell put in the formula

=istext(A2)

If it returns true, then this is you problem. Select the column and format
it as a data format. Now do data=>TexttoColumns, select delimited and on
the next dialog, make sure the delimiter comma and nothing else (or any
delimiter not in the column). this will cause Excel to reevaluate the dates
and convert them to dates. Your istext formula should then show false.

When you copy and paste the data to another location, Excel probably
converted it to dates, so the problem is exacerbated.

Another way to convert would be to select the column and do

Edit=>Replace
what: \
With: \

this assumes the dates have a \ in them.

 

[jeffP]

Tom,
This information is good, and I'm learning but it still didn't help this
situation.Istext is false on all columns of data. And it's not just dates. I
tried to use the advanced filter with the custnum column (straight 4 digit
numeric code) and it just brings the entire data list without filtering
anything out.
I'm lost here so any help or ideas would be appreciated.

 

[Tom Ogilvy]

Try using the autofilter.

 

[jeffP]

Tom,
The auto filter does work and I guess that's the way I have to go to get
this project done. I was trying to do the entire thing programmatically
(loop an If compare) and eliminate users needing to use the autofilter.
Plus I got stubborn and intrigued as to why I can't compare to this former
*dbf. Oh well. Thanks for your help and if you have any other ideas I
really am curious as to why this won't work.

 

[Tom Ogilvy]

You can manage an autofilter with code, same as an advanced filter. Turn on
the macro recorder and do it manually to see the basic syntax.