Filtering a column with DATES in Excel 1

[Suggie]

Hello,

I have a column with dates that either end as yy or yyyy.
For example:

09/27/04
07/07/2003

What I am trying to do is delete any rows where the date is
ends in 2003 or 03.

I have tried a custom filter and text to columns but it's not working?

Any ideas welcomed and appreciated.

TIA,
Mark

 

[klmorgan]

How about using =Year() function ia spare cell in each record then using autofilter?

Regards

Keith

 

[SkipVought]

Mark,

First determine if you have REAL dates -- select the column and Format the cells as General. Each REAL DATE will change to a NUMBER, like 38285, which the the serial value for today's date.

If your dates are REAL, just format them (Format/Cells - Number Tab...) with the desired date format.

Now to filter on YEAR -- Format the date column using a Custom format yyyy. This will display the year only. Filter and delete.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Guest_imported]

Mark,

You refer to a "custom" filter. If this refers to "Advanced filter", you can simply use the following formula in your criteria.

=YEAR(field1)=2003

where you replace "field1" with whatever field name you're using for your Date field.

Remember 2 things when using a formula in the criteria:
1) You'll get "#NAME?", which is normal in this situation, and
2) Be sure to leave the cell above the formula BLANK, but still assign the range name for the criteria to BOTH cells - i.e. the one with the formula and the BLANK cell.

Using this method, you don't require an extra field with a formula for every record. You simply need the ONE criteria with the one formula.

I hope this helps.

Regards,

Dale Watson

 

[Suggie]

Thanks.

I've just checked and I don't have REAL dates? This is an import from an in-house system. When I format the cells as general I get the same data displayed. I actually cannot format it any other way it seems.

When I try the =year() function I get #value!

Any ideas?

TIA,

 

[SkipVought]

You will have to use STRING function like Left, Right, Mid, Search, to parse each string...

If all you data is like this
[tt]
09/27/04
07/07/2003
[/tt]
then if your date is in A1
[tt]
=DATE(IF(LEN(RIGHT(A1,LEN(A1)-SEARCH("/",A1,4)))=2,IF(RIGHT(A1,LEN(A1)-SEARCH("/",A1,4))<"50","20","19")&RIGHT(A1,LEN(A1)-SEARCH("/",A1,4)),RIGHT(A1,LEN(A1)-SEARCH("/",A1,4))),LEFT(A1,2),MID(A1,4,2))
[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[SkipVought]

Here's a simpler version
[tt]
=DATE(IF(LEN(A1)=8,IF(RIGHT(A1,2)<"50","20","19")&RIGHT(A1,2),RIGHT(A1,4)),LEFT(A1,2),MID(A1,4,2))
[/tt]

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Suggie]

Worked liked an absolute trick...much appreciated. Thanks Skip.