Search code examples
excelexcel-2007

Custom filter for range of years to target year of birth

In Excel 2007 I have a column with thousands of numbers, where the first part of the number is the date of birth; mmddYYxxxxx. I'm looking for a way to use a custom filter to get only those with date of birth (YY) between 00 and 15.

Example:

1103**81**12345
0112**04**12345

I would like the filter to hide the first entry, but not the last one. Any ideas?

Solution

  • You can use a helper cell to determine whether the data falls into your desired date range. Then you will be able to filter on the helper cell.

    Assuming your data starts in A2, the following formula will determine if you want to "skip" or "keep" the data. 

    =IF(OR(VALUE(MID(A2,4,2))>15,VALUE(MID(A2,4,2))<0),"Skip","Keep")

    helpercell

    You can then filter out the "Skips" so that only your desired data remains:

    helpercell2