| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto filter Dates by Last Number
In Excel 2000, I'm trying to run an autofilter on a list of dates and I only
want it to return dates where the value of the last digit is a 1 or a 6. So I select 'Custom' and 'Ends with' 1, and then check the 'or' box and selct 'ends with' 6. This should work, but it doesn't seem to be reading dates in the way I need it to. I tried pasting values, no luck. I tried changing the format of the cells away from date, but then it gives me a number with a different ending digit. Very frustrating. Does anyone know how to teach Excel to read a date as a number for the autoformat? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto filter Dates by Last Number
Not clear what your last number (1 or 6) means ..
Perhaps you could try filtering on a helper col, something along these lines: Assuming dates in A2 down, with last number to be taken from the "day" Place in say, C2: =IF(OR(RIGHT(TEXT(A2,"d"))+0={1,6}),"X","") Copy C2 down to flag out designated rows with "X" Then just autofilter on col C, select: X -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dalovindj" wrote: In Excel 2000, I'm trying to run an autofilter on a list of dates and I only want it to return dates where the value of the last digit is a 1 or a 6. So I select 'Custom' and 'Ends with' 1, and then check the 'or' box and selct 'ends with' 6. This should work, but it doesn't seem to be reading dates in the way I need it to. I tried pasting values, no luck. I tried changing the format of the cells away from date, but then it gives me a number with a different ending digit. Very frustrating. Does anyone know how to teach Excel to read a date as a number for the autoformat? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto filter Dates by Last Number
"Max" wrote: Not clear what your last number (1 or 6) means .. Perhaps you could try filtering on a helper col, something along these lines: Assuming dates in A2 down, with last number to be taken from the "day" Place in say, C2: =IF(OR(RIGHT(TEXT(A2,"d"))+0={1,6}),"X","") Copy C2 down to flag out designated rows with "X" Then just autofilter on col C, select: X -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dalovindj" wrote: In Excel 2000, I'm trying to run an autofilter on a list of dates and I only want it to return dates where the value of the last digit is a 1 or a 6. So I select 'Custom' and 'Ends with' 1, and then check the 'or' box and selct 'ends with' 6. This should work, but it doesn't seem to be reading dates in the way I need it to. I tried pasting values, no luck. I tried changing the format of the cells away from date, but then it gives me a number with a different ending digit. Very frustrating. Does anyone know how to teach Excel to read a date as a number for the autoformat? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto filter Dates by Last Number
Dates look like this:
1/2/2006 4/5/2004 3/8/1976 I just want to retun rows where the last digit of the year is a 6 or a 1. "Max" wrote: Not clear what your last number (1 or 6) means .. Perhaps you could try filtering on a helper col, something along these lines: Assuming dates in A2 down, with last number to be taken from the "day" Place in say, C2: =IF(OR(RIGHT(TEXT(A2,"d"))+0={1,6}),"X","") Copy C2 down to flag out designated rows with "X" Then just autofilter on col C, select: X -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dalovindj" wrote: In Excel 2000, I'm trying to run an autofilter on a list of dates and I only want it to return dates where the value of the last digit is a 1 or a 6. So I select 'Custom' and 'Ends with' 1, and then check the 'or' box and selct 'ends with' 6. This should work, but it doesn't seem to be reading dates in the way I need it to. I tried pasting values, no luck. I tried changing the format of the cells away from date, but then it gives me a number with a different ending digit. Very frustrating. Does anyone know how to teach Excel to read a date as a number for the autoformat? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto filter Dates by Last Number
That formula did the trick (I just witched the D to a Y". Thanks.
"Max" wrote: Not clear what your last number (1 or 6) means .. Perhaps you could try filtering on a helper col, something along these lines: Assuming dates in A2 down, with last number to be taken from the "day" Place in say, C2: =IF(OR(RIGHT(TEXT(A2,"d"))+0={1,6}),"X","") Copy C2 down to flag out designated rows with "X" Then just autofilter on col C, select: X -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dalovindj" wrote: In Excel 2000, I'm trying to run an autofilter on a list of dates and I only want it to return dates where the value of the last digit is a 1 or a 6. So I select 'Custom' and 'Ends with' 1, and then check the 'or' box and selct 'ends with' 6. This should work, but it doesn't seem to be reading dates in the way I need it to. I tried pasting values, no luck. I tried changing the format of the cells away from date, but then it gives me a number with a different ending digit. Very frustrating. Does anyone know how to teach Excel to read a date as a number for the autoformat? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto filter Dates by Last Number
Glad you got it adapted to suit <g.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dalovindj" wrote in message ... That formula did the trick (I just switched the "D" to a "Y"). Thanks. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto filter Dates by Last Number
Given it's the year your looking at, you can avoid using a helper column with
the following formula: =mod(year(a1),5)=1 -- Regards, Fred "dalovindj" wrote in message ... Dates look like this: 1/2/2006 4/5/2004 3/8/1976 I just want to retun rows where the last digit of the year is a 6 or a 1. "Max" wrote: Not clear what your last number (1 or 6) means .. Perhaps you could try filtering on a helper col, something along these lines: Assuming dates in A2 down, with last number to be taken from the "day" Place in say, C2: =IF(OR(RIGHT(TEXT(A2,"d"))+0={1,6}),"X","") Copy C2 down to flag out designated rows with "X" Then just autofilter on col C, select: X -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dalovindj" wrote: In Excel 2000, I'm trying to run an autofilter on a list of dates and I only want it to return dates where the value of the last digit is a 1 or a 6. So I select 'Custom' and 'Ends with' 1, and then check the 'or' box and selct 'ends with' 6. This should work, but it doesn't seem to be reading dates in the way I need it to. I tried pasting values, no luck. I tried changing the format of the cells away from date, but then it gives me a number with a different ending digit. Very frustrating. Does anyone know how to teach Excel to read a date as a number for the autoformat? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to filter out all the partial dates from a big list of dates? | Excel Discussion (Misc queries) | |||
| in data/filter/auto filter | Excel Worksheet Functions | |||
| Applying auto-filter with large number of rows | Excel Discussion (Misc queries) | |||
| Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
| Auto filter not displaying number of results in status bar. | Excel Discussion (Misc queries) | |||
Linear Mode
