Date Sorting (Excel 2000 (9.0.6926 SP-3))

Topic

New Reply

I have a worksheet with dates (dd/mm/yyyy) in column A. The downloaded dates were not ordered the way I wanted them. I made certain that the cells were formatted as dates. I was amazed to discover that “data/sort” sorted by day and month but ignored the year. I overcame the problem by segregating the sheet into year sections with empty rows in between and then sorting by selecting and sorting each section.

I’ve attached a file showing the data sorted they way I want it (October 2005 – February 2006) and the way that Excel sorts it.

Is this a ‘known bug’ or do I have an obscure preference set somewhere that I cannot find?

Thanks,

baumgrenze

Reply | Quote

Replies

There are spaces before the date values, and this makes Excel interpret the values as text, not as dates.

Option 1: select the dates.
Select Data | Text to Columns…
Select Delimited and click Next.
Specify Space as delimiter and click Next.
Specify that you want to skip the first column, and that the second column is a date (format MDY).
Click Finish.
The values should now be sorted correcty.

Option 2: say the data are in A1:A100 (or whatever)
In B1, enter the formula =TRIM(A1)
Fill down as far as the data go.
Sort on column B.

Reply | Quote

The dates in your worksheet are text values not date values, therefore they are going to sort in text order not date order. To have them sort into date sequence, you will need to convert them into dates. Enter the following formula into cell B2 (assuming that the dates start in A2 as they do in the workbook you uploaded). Insert a new empty column B if necessary.


=DATE(RIGHT(TRIM(A2),4),LEFT(TRIM(A2),2), MID(TRIM(A2),4,2))


Copy that formula down as far as necessary. You can now sort on column B and get the order you want. You can also copy column B and Paste/Values back over Column A and delete the new column B if you don’t want both columns.

Reply | Quote

Thank you both Hans and Legare,

Innocent me! I selected the contents of that colum and invoked “format/cells” and formatted them as dates. They were formatted as ‘general’ when I checked after my first sort failed. I went back and checked with “format/cells” and was told that they were dates. It still does.

I consider this a programming flaw. Have others complained to Redmond about this? Perhaps I should complain to Fidelity who supplied the data as a comma delimited *.csv file. When I open the file in Word I can see all kind of unneeded ‘spaces’ padding the file.

The cell contents changed when I selected one of the sets and did an “edit/replace” and removed all the padding spaces.

In another application in the MS Office Suite (MS Word) I can elect to see all the “formatting marks.” I’ve always done this. I copy and paste paragraph marks all the time to apply formatting from one paragraph to another one. Is it possible to do this in the ‘seamless’ Office Suite for Excel? I do have “view all objects” set under “options.”

I’ll try to remember to delete spaces next time I try this task. It is one I do only quarterly or so.

baumgrenze

Reply | Quote

I don’t think the fault lies with Microsoft. In a comma-delimited file, there should be no superfluous spaces padding the data.

Excel is not a word processor, it does not have the option to show non-printing characters on-screen.

Reply | Quote

I found the willingness of Excel to “format” the data as a “date” and to respond, when I checked after formatting, that it was perceived as a date, misleading.

If the program does not want to format padded data, it should say so explicitly, not imply that the data has been reformatted.

Help me understand why this is a ‘hard headed’ attitude, especially if the padding is hard to detect.

This is even more confusing than I thought. I just copied a number from Word with 3 leading spaces and pasted it into a cell. The spaces were deleted. They were not transferred to the formula bar. I then entered a number in the formula bar, padded it with 3 leading spaces, and entered it into a cell formatted as “general.” The leading spaces were not there. Aha! If I enter 03/08/06, then I can retain the padding. Perhaps this ‘feature’ allows me to have ‘fractions’ like “1/2” and not have them interpreted as dates. It is a bunch to remember. I know I have ‘fought’ with Excel when I’ve tried to paste information like drill or bolt sizes and have them interpreted as text. The same goes for numbers separated by a “-“!

Is it true (if I remember next time) that if I first paste the copied file into Word and pad the fractions with a space I can overcome this annoying ‘feature?’

Call me a reluctant, stream of consciousness learner. Pardon my ramble. Correct me if I still do not understand.

Thanks,

baumgrenze

Reply | Quote

The problem is that when you change the format, it changes the cell format not what is in the cell. In addition, number formats (including date formats which is just another number format) apply only to numbers. If you place text into a cell that is formatted to display numbers as dates, the number format is ignored and the text is entered into the cell as text. The text from the CSV file had extra spaces in front of it and therefore Excel recognized it as text not as a date. When you go back and look at the cell format, that is exactly what you are looking at, the format that the cell will use if a numeric value is entered into the cell.

Reply | Quote

Thank you Legare,

The way it works is clear to me now!

baumgrenze

Reply | Quote