| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
matching dates from imported *.dbf file?
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? -- jeff |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching dates from imported *.dbf file?
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. -- regards, Tom Ogilvy "jeffP" wrote in message ... 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? -- jeff |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching dates from imported *.dbf file?
Try using the autofilter.
-- Regards, Tom Ogilvy "jeffP" wrote in message ... 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. -- jeffP "Tom Ogilvy" wrote in message ... 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. -- regards, Tom Ogilvy "jeffP" wrote in message ... 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 dat a cell. It won't match up even with itself! Could you explain to me what I am missing? -- jeff |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching dates from imported *.dbf file?
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. -- jeffP "Tom Ogilvy" wrote in message ... Try using the autofilter. -- Regards, Tom Ogilvy "jeffP" wrote in message ... 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. -- jeffP "Tom Ogilvy" wrote in message ... 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. -- regards, Tom Ogilvy "jeffP" wrote in message ... 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 dat a cell. It won't match up even with itself! Could you explain to me what I am missing? -- jeff |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
matching dates from imported *.dbf file?
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. -- Regards, Tom Ogilvy "jeffP" wrote in message ... 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. -- jeffP "Tom Ogilvy" wrote in message ... Try using the autofilter. -- Regards, Tom Ogilvy "jeffP" wrote in message ... 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. -- jeffP "Tom Ogilvy" wrote in message ... 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. -- regards, Tom Ogilvy "jeffP" wrote in message ... 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 dat a cell. It won't match up even with itself! Could you explain to me what I am missing? -- jeff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Imported dates | Excel Discussion (Misc queries) | |||
| imported text data converting to dates | Excel Discussion (Misc queries) | |||
| Imported numbers change to dates | Excel Discussion (Misc queries) | |||
| Excel 2003 Trashes Imported Dates | Excel Discussion (Misc queries) | |||
| Using imported Query & Calculating Dates | Excel Worksheet Functions | |||
Linear Mode
