| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem filtering by data 12/01/2002
Hi ,
I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
Hi manfareed
See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
Hi,
Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
You can read this below the macro. Use the US mm/dd/yyyy date format
Use this to filter for all women born between 23 Feb 1947 and 7 May 1988 from the Netherlands and the USA (column A, C and D in my example). I use two criteria in field 1 and 4 (2 is the maximum for AutoFilter) rng.AutoFilter Field:=1, Criteria1:="=Netherlands", Operator:=xlOr, Criteria2:="=USA" rng.AutoFilter Field:=3, Criteria1:="=F" rng.AutoFilter Field:=4, Criteria1:="=02/23/1947" , _ Operator:=xlAnd, Criteria2:="<=05/07/1988" ' Use always the US mm/dd/yyyy format -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
"Ron de Bruin" wrote: You can read this below the macro. Use the US mm/dd/yyyy date format Use this to filter for all women born between 23 Feb 1947 and 7 May 1988 from the Netherlands and the USA (column A, C and D in my example). I use two criteria in field 1 and 4 (2 is the maximum for AutoFilter) rng.AutoFilter Field:=1, Criteria1:="=Netherlands", Operator:=xlOr, Criteria2:="=USA" rng.AutoFilter Field:=3, Criteria1:="=F" rng.AutoFilter Field:=4, Criteria1:="=02/23/1947" , _ Operator:=xlAnd, Criteria2:="<=05/07/1988" ' Use always the US mm/dd/yyyy format -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks Hi Ron, I can't get it to filter for anything "greater than " but otherwise it works. Also there is a lot of data in UK format. Is there a way to convert them to US format i.e. from dd/mm/yyyy to mm/dd/yyyy. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
Only in the code use the US date format
No problem if the format in the worksheet is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... "Ron de Bruin" wrote: You can read this below the macro. Use the US mm/dd/yyyy date format Use this to filter for all women born between 23 Feb 1947 and 7 May 1988 from the Netherlands and the USA (column A, C and D in my example). I use two criteria in field 1 and 4 (2 is the maximum for AutoFilter) rng.AutoFilter Field:=1, Criteria1:="=Netherlands", Operator:=xlOr, Criteria2:="=USA" rng.AutoFilter Field:=3, Criteria1:="=F" rng.AutoFilter Field:=4, Criteria1:="=02/23/1947" , _ Operator:=xlAnd, Criteria2:="<=05/07/1988" ' Use always the US mm/dd/yyyy format -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks Hi Ron, I can't get it to filter for anything "greater than " but otherwise it works. Also there is a lot of data in UK format. Is there a way to convert them to US format i.e. from dd/mm/yyyy to mm/dd/yyyy. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
Hi Ron,
I can leave the code in UK format in the code and the filter works if I do an "equal to " filter but I can't filter for a dates "greater than" regardless of the format. USIDNUMBER STARTDATE SURNAME FIRSTNAME 119509 02/04/2007 mmm aaa Aix-en-Provence AT Sales Consultant Sévilla Aurélia lll ppp "Ron de Bruin" wrote: Only in the code use the US date format No problem if the format in the worksheet is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... "Ron de Bruin" wrote: You can read this below the macro. Use the US mm/dd/yyyy date format Use this to filter for all women born between 23 Feb 1947 and 7 May 1988 from the Netherlands and the USA (column A, C and D in my example). I use two criteria in field 1 and 4 (2 is the maximum for AutoFilter) rng.AutoFilter Field:=1, Criteria1:="=Netherlands", Operator:=xlOr, Criteria2:="=USA" rng.AutoFilter Field:=3, Criteria1:="=F" rng.AutoFilter Field:=4, Criteria1:="=02/23/1947" , _ Operator:=xlAnd, Criteria2:="<=05/07/1988" ' Use always the US mm/dd/yyyy format -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks Hi Ron, I can't get it to filter for anything "greater than " but otherwise it works. Also there is a lot of data in UK format. Is there a way to convert them to US format i.e. from dd/mm/yyyy to mm/dd/yyyy. Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
= (equal) filter on the text in the cell
filter on the cell value Are you sure you have real dates in the cells and not text dates ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi Ron, I can leave the code in UK format in the code and the filter works if I do an "equal to " filter but I can't filter for a dates "greater than" regardless of the format. USIDNUMBER STARTDATE SURNAME FIRSTNAME 119509 02/04/2007 mmm aaa Aix-en-Provence AT Sales Consultant Sévilla Aurélia lll ppp "Ron de Bruin" wrote: Only in the code use the US date format No problem if the format in the worksheet is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... "Ron de Bruin" wrote: You can read this below the macro. Use the US mm/dd/yyyy date format Use this to filter for all women born between 23 Feb 1947 and 7 May 1988 from the Netherlands and the USA (column A, C and D in my example). I use two criteria in field 1 and 4 (2 is the maximum for AutoFilter) rng.AutoFilter Field:=1, Criteria1:="=Netherlands", Operator:=xlOr, Criteria2:="=USA" rng.AutoFilter Field:=3, Criteria1:="=F" rng.AutoFilter Field:=4, Criteria1:="=02/23/1947" , _ Operator:=xlAnd, Criteria2:="<=05/07/1988" ' Use always the US mm/dd/yyyy format -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks Hi Ron, I can't get it to filter for anything "greater than " but otherwise it works. Also there is a lot of data in UK format. Is there a way to convert them to US format i.e. from dd/mm/yyyy to mm/dd/yyyy. Thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
Hi,
Its a real date. Filter works with equal but not . I checked the format and its "date". Thanks, Manir "Ron de Bruin" wrote: = (equal) filter on the text in the cell filter on the cell value Are you sure you have real dates in the cells and not text dates ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi Ron, I can leave the code in UK format in the code and the filter works if I do an "equal to " filter but I can't filter for a dates "greater than" regardless of the format. USIDNUMBER STARTDATE SURNAME FIRSTNAME 119509 02/04/2007 mmm aaa Aix-en-Provence AT Sales Consultant Sévilla Aurélia lll ppp "Ron de Bruin" wrote: Only in the code use the US date format No problem if the format in the worksheet is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... "Ron de Bruin" wrote: You can read this below the macro. Use the US mm/dd/yyyy date format Use this to filter for all women born between 23 Feb 1947 and 7 May 1988 from the Netherlands and the USA (column A, C and D in my example). I use two criteria in field 1 and 4 (2 is the maximum for AutoFilter) rng.AutoFilter Field:=1, Criteria1:="=Netherlands", Operator:=xlOr, Criteria2:="=USA" rng.AutoFilter Field:=3, Criteria1:="=F" rng.AutoFilter Field:=4, Criteria1:="=02/23/1947" , _ Operator:=xlAnd, Criteria2:="<=05/07/1988" ' Use always the US mm/dd/yyyy format -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks Hi Ron, I can't get it to filter for anything "greater than " but otherwise it works. Also there is a lot of data in UK format. Is there a way to convert them to US format i.e. from dd/mm/yyyy to mm/dd/yyyy. Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
Hi manfareed
Send me your workbook private then i look at it when I am home -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Its a real date. Filter works with equal but not . I checked the format and its "date". Thanks, Manir "Ron de Bruin" wrote: = (equal) filter on the text in the cell filter on the cell value Are you sure you have real dates in the cells and not text dates ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi Ron, I can leave the code in UK format in the code and the filter works if I do an "equal to " filter but I can't filter for a dates "greater than" regardless of the format. USIDNUMBER STARTDATE SURNAME FIRSTNAME 119509 02/04/2007 mmm aaa Aix-en-Provence AT Sales Consultant Sévilla Aurélia lll ppp "Ron de Bruin" wrote: Only in the code use the US date format No problem if the format in the worksheet is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... "Ron de Bruin" wrote: You can read this below the macro. Use the US mm/dd/yyyy date format Use this to filter for all women born between 23 Feb 1947 and 7 May 1988 from the Netherlands and the USA (column A, C and D in my example). I use two criteria in field 1 and 4 (2 is the maximum for AutoFilter) rng.AutoFilter Field:=1, Criteria1:="=Netherlands", Operator:=xlOr, Criteria2:="=USA" rng.AutoFilter Field:=3, Criteria1:="=F" rng.AutoFilter Field:=4, Criteria1:="=02/23/1947" , _ Operator:=xlAnd, Criteria2:="<=05/07/1988" ' Use always the US mm/dd/yyyy format -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks Hi Ron, I can't get it to filter for anything "greater than " but otherwise it works. Also there is a lot of data in UK format. Is there a way to convert them to US format i.e. from dd/mm/yyyy to mm/dd/yyyy. Thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem filtering by data 12/01/2002
Hi Ron,
I have emailed to your email address as per your website. Its from my work address. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed Send me your workbook private then i look at it when I am home -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Its a real date. Filter works with equal but not . I checked the format and its "date". Thanks, Manir "Ron de Bruin" wrote: = (equal) filter on the text in the cell filter on the cell value Are you sure you have real dates in the cells and not text dates ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi Ron, I can leave the code in UK format in the code and the filter works if I do an "equal to " filter but I can't filter for a dates "greater than" regardless of the format. USIDNUMBER STARTDATE SURNAME FIRSTNAME 119509 02/04/2007 mmm aaa Aix-en-Provence AT Sales Consultant Sévilla Aurélia lll ppp "Ron de Bruin" wrote: Only in the code use the US date format No problem if the format in the worksheet is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... "Ron de Bruin" wrote: You can read this below the macro. Use the US mm/dd/yyyy date format Use this to filter for all women born between 23 Feb 1947 and 7 May 1988 from the Netherlands and the USA (column A, C and D in my example). I use two criteria in field 1 and 4 (2 is the maximum for AutoFilter) rng.AutoFilter Field:=1, Criteria1:="=Netherlands", Operator:=xlOr, Criteria2:="=USA" rng.AutoFilter Field:=3, Criteria1:="=F" rng.AutoFilter Field:=4, Criteria1:="=02/23/1947" , _ Operator:=xlAnd, Criteria2:="<=05/07/1988" ' Use always the US mm/dd/yyyy format -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi, Sorry but I can't see anything which is specific to my query. Thanks, Manir "Ron de Bruin" wrote: Hi manfareed See the tips below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "manfareed" wrote in message ... Hi , I have data in 13 columns and the second column contains the "start dates". The "start dates" are in the following format "26/09/2007" etc. [UK Date format]. I need to filter for dates greater than "12/01/2002". So far I have been unsuccessful. I have tried to reformat the "start date" column as date and as text but I still cannot filter for "greater than" via the autofilter. "12/01/2002" is not an actual date in the " start date" column. I don't mind if I have to use a macro to get the required result. Thanks Hi Ron, I can't get it to filter for anything "greater than " but otherwise it works. Also there is a lot of data in UK format. Is there a way to convert them to US format i.e. from dd/mm/yyyy to mm/dd/yyyy. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 filtering | Excel Programming | |||
| Filtering problem | Excel Programming | |||
| Excel 2002: Problem in pasting copied data to Coda Financial | Excel Discussion (Misc queries) | |||
| Excel 2002: Problem in pasting copied data to Coda Financial Acco | Excel Discussion (Misc queries) | |||
| Excel 2002 - Filtering numbers | Excel Discussion (Misc queries) | |||
Linear Mode
