[Meaux30]

Good morning - I could really use some help.
I need to sort a sheet by date and have it sort by year first and then by month and day. My goal is to delete everything before the current year. Just doing a sort lumps all of a month together, for instance: June will include 1999 - 2011 and then the list will move to July. I tried a custom date format but couldn't make that work. Any idea?

The problem was I hadn't imported the data correctly - It came from and ASCII file and instead of importing it as a date field I imported it as text and then formatted it to date. I didn't realize that could change the behavior.

Edited by Meaux30, 28 July 2005 - 10:08 AM.

[Advertisements]

Meaux,
There may be a few ways to overcome your problem. It depends what the ascii text looks like.


Example 1.
assume your dates are in column A starting at row 2
assume column D is clear

In column D type the formula =datevalue(A2) to attempt to determine the date serial number
Copy the formula down
Format column D as some type of date (for display purposes only)
Sort by column D


Example 2.
If the year is always 4 characters and always at the end eg. 12-Mar-1999
In column D type the formula =right(A2,4) to determine the year only, sort by column D and just delete lines for the second last year.


If this does not work or only partially works zip up your file and attach it or at least key in some sample data.


Hope this helps
David

[dsm]

Meaux,
There may be a few ways to overcome your problem. It depends what the ascii text looks like.


Example 1.
assume your dates are in column A starting at row 2
assume column D is clear

In column D type the formula =datevalue(A2) to attempt to determine the date serial number
Copy the formula down
Format column D as some type of date (for display purposes only)
Sort by column D


Example 2.
If the year is always 4 characters and always at the end eg. 12-Mar-1999
In column D type the formula =right(A2,4) to determine the year only, sort by column D and just delete lines for the second last year.


If this does not work or only partially works zip up your file and attach it or at least key in some sample data.


Hope this helps
David