If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Autofilter to exclude dates
I'm working with data that has about 19,000 rows. The information is date
specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#2
|
|||
|
|||
Using Autofilter to exclude dates
Hmmm, it should work as you describe; I do this all the time and have
no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15*pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#3
|
|||
|
|||
Using Autofilter to exclude dates
The column in questions references another column that is a date/time stamp.
When I first noticed problem I thought that might be the issue. So the workflow I employed was: first I entered the string "=text (G14, "mm/dd/yy" which made the cell information text.Then I did a copy on the column, then "paste special-values" sequence followed by reformatting the cells as dates. That was the best way I could figure to eliminate the time portion of the stamp. I'm sure I've successfully excludedthis before without going through all of that, but I'm just trying to eliminate possibilites at this point. "Reitanos" wrote: Hmmm, it should work as you describe; I do this all the time and have no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15 pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#4
|
|||
|
|||
Using Autofilter to exclude dates
Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid
of the time portion? -- David Biddulph "Ian" wrote in message ... The column in questions references another column that is a date/time stamp. When I first noticed problem I thought that might be the issue. So the workflow I employed was: first I entered the string "=text (G14, "mm/dd/yy" which made the cell information text.Then I did a copy on the column, then "paste special-values" sequence followed by reformatting the cells as dates. That was the best way I could figure to eliminate the time portion of the stamp. I'm sure I've successfully excludedthis before without going through all of that, but I'm just trying to eliminate possibilites at this point. "Reitanos" wrote: Hmmm, it should work as you describe; I do this all the time and have no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15 pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#5
|
|||
|
|||
Using Autofilter to exclude dates
I tried that but it set the date to 1/0/1900.
"David Biddulph" wrote: Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid of the time portion? -- David Biddulph "Ian" wrote in message ... The column in questions references another column that is a date/time stamp. When I first noticed problem I thought that might be the issue. So the workflow I employed was: first I entered the string "=text (G14, "mm/dd/yy" which made the cell information text.Then I did a copy on the column, then "paste special-values" sequence followed by reformatting the cells as dates. That was the best way I could figure to eliminate the time portion of the stamp. I'm sure I've successfully excludedthis before without going through all of that, but I'm just trying to eliminate possibilites at this point. "Reitanos" wrote: Hmmm, it should work as you describe; I do this all the time and have no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15 pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#6
|
|||
|
|||
Using Autofilter to exclude dates
Of course I got it wrong!
=MOD(G14,1) gives just the time. =INT(G14) gives just the date part. -- David Biddulph "Ian" wrote in message ... I tried that but it set the date to 1/0/1900. "David Biddulph" wrote: Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid of the time portion? -- David Biddulph "Ian" wrote in message ... The column in questions references another column that is a date/time stamp. When I first noticed problem I thought that might be the issue. So the workflow I employed was: first I entered the string "=text (G14, "mm/dd/yy" which made the cell information text.Then I did a copy on the column, then "paste special-values" sequence followed by reformatting the cells as dates. That was the best way I could figure to eliminate the time portion of the stamp. I'm sure I've successfully excludedthis before without going through all of that, but I'm just trying to eliminate possibilites at this point. "Reitanos" wrote: Hmmm, it should work as you describe; I do this all the time and have no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15 pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#7
|
|||
|
|||
Using Autofilter to exclude dates
Is there a reason that you're not just formatting it to LOOK
different? Another way to get rid of the time is to use the TRUNC function to lop it off. On Jul 31, 3:20*pm, Ian wrote: I tried that but it set the date to 1/0/1900. "David Biddulph" wrote: Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid of the time portion? -- David Biddulph "Ian" wrote in message ... The column in questions references another column that is a date/time stamp. When I first noticed problem I thought that might be the issue. So the workflow I employed was: first I entered the string "=text (G14, "mm/dd/yy" which made the cell information text.Then I did a copy on the column, then "paste special-values" sequence followed by reformatting the cells as dates. That was the best way I could figure to eliminate the time portion of the stamp. I'm sure I've successfully excludedthis before without going through all of that, but I'm just trying to eliminate possibilites at this point. "Reitanos" wrote: Hmmm, it should work as you describe; I do this all the time and have no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15 pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#8
|
|||
|
|||
Using Autofilter to exclude dates
It appears that how the data is formatted is not the issue. I did get the
cells to reflect only the date (although I took the long way around) but I still can't exclude the date in queston. It is helpful to know that you were able to do it becaue I'm sure I've done it before on past data sets. This one has me stumped. Incidentally, when you excluded the date, did you have multiple rows that had that particular date and it exluded all of them? It does appear to exclude one row of the date, but that's not really helpful. "Reitanos" wrote: Is there a reason that you're not just formatting it to LOOK different? Another way to get rid of the time is to use the TRUNC function to lop it off. On Jul 31, 3:20 pm, Ian wrote: I tried that but it set the date to 1/0/1900. "David Biddulph" wrote: Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid of the time portion? -- David Biddulph "Ian" wrote in message ... The column in questions references another column that is a date/time stamp. When I first noticed problem I thought that might be the issue. So the workflow I employed was: first I entered the string "=text (G14, "mm/dd/yy" which made the cell information text.Then I did a copy on the column, then "paste special-values" sequence followed by reformatting the cells as dates. That was the best way I could figure to eliminate the time portion of the stamp. I'm sure I've successfully excludedthis before without going through all of that, but I'm just trying to eliminate possibilites at this point. "Reitanos" wrote: Hmmm, it should work as you describe; I do this all the time and have no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15 pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#9
|
|||
|
|||
Using Autofilter to exclude dates
The "does not equal" condition will exclude more than one row at a time.
-- David Biddulph "Ian" wrote in message ... It appears that how the data is formatted is not the issue. I did get the cells to reflect only the date (although I took the long way around) but I still can't exclude the date in queston. It is helpful to know that you were able to do it becaue I'm sure I've done it before on past data sets. This one has me stumped. Incidentally, when you excluded the date, did you have multiple rows that had that particular date and it exluded all of them? It does appear to exclude one row of the date, but that's not really helpful. "Reitanos" wrote: Is there a reason that you're not just formatting it to LOOK different? Another way to get rid of the time is to use the TRUNC function to lop it off. On Jul 31, 3:20 pm, Ian wrote: I tried that but it set the date to 1/0/1900. "David Biddulph" wrote: Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid of the time portion? -- David Biddulph "Ian" wrote in message ... The column in questions references another column that is a date/time stamp. When I first noticed problem I thought that might be the issue. So the workflow I employed was: first I entered the string "=text (G14, "mm/dd/yy" which made the cell information text.Then I did a copy on the column, then "paste special-values" sequence followed by reformatting the cells as dates. That was the best way I could figure to eliminate the time portion of the stamp. I'm sure I've successfully excludedthis before without going through all of that, but I'm just trying to eliminate possibilites at this point. "Reitanos" wrote: Hmmm, it should work as you describe; I do this all the time and have no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15 pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
#10
|
|||
|
|||
Using Autofilter to exclude dates
Yes, it should. But for some reason it is not doing it. But at least I know
others are getting to work. "David Biddulph" wrote: The "does not equal" condition will exclude more than one row at a time. -- David Biddulph "Ian" wrote in message ... It appears that how the data is formatted is not the issue. I did get the cells to reflect only the date (although I took the long way around) but I still can't exclude the date in queston. It is helpful to know that you were able to do it becaue I'm sure I've done it before on past data sets. This one has me stumped. Incidentally, when you excluded the date, did you have multiple rows that had that particular date and it exluded all of them? It does appear to exclude one row of the date, but that's not really helpful. "Reitanos" wrote: Is there a reason that you're not just formatting it to LOOK different? Another way to get rid of the time is to use the TRUNC function to lop it off. On Jul 31, 3:20 pm, Ian wrote: I tried that but it set the date to 1/0/1900. "David Biddulph" wrote: Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid of the time portion? -- David Biddulph "Ian" wrote in message ... The column in questions references another column that is a date/time stamp. When I first noticed problem I thought that might be the issue. So the workflow I employed was: first I entered the string "=text (G14, "mm/dd/yy" which made the cell information text.Then I did a copy on the column, then "paste special-values" sequence followed by reformatting the cells as dates. That was the best way I could figure to eliminate the time portion of the stamp. I'm sure I've successfully excludedthis before without going through all of that, but I'm just trying to eliminate possibilites at this point. "Reitanos" wrote: Hmmm, it should work as you describe; I do this all the time and have no problem. Are your dates all stored as actual dates and not text? I've just double-checked and it absolutely excludes 2/18/2008 when I use "Does not equal 2/18" - note that the year is implied as the current year and it will not hide 2/18/2007. Please clarify. On Jul 31, 1:15 pm, Ian wrote: I'm working with data that has about 19,000 rows. The information is date specific, and there are scores of rows for each date.. I was working with February data recently and was trying to use the autofilter to exclude February 18 (President's Day) as in "Does not equal" 2/18/2008. Much to my surprise it doesn't appear to work that way. It will exclude one row of February data, but not all. I can use the "is less than" and "is greater than" path, but it seems like a long way around. I thought I had used the autofilter before to exclude all the rows of a specific date, but apparently not. Any ideas out there? |
|
Thread Tools | |
Display Modes | |
|
|