sorting dates in excel

[derksj]

I have attached a excel file
can someone tell me how i can sort the middel collum from old date t
new dat

+-------------------------------------------------------------------
|Filename: sortingdates.zip
|Download: http://www.excelforum.com/attachment.php?postid=4512
+-------------------------------------------------------------------

 

[robert111]

I have looked at your spreadsheet but am unclear what you want. Do yo
wish to sort by period first, then date, or do you wish to change th
date format

 

[broro183]

Hi,
There are probably better ways of doing this but the below works b
creating a "helper column".

1) Create a lookup table for Months somewhere out of the way e
January, February, etc in cells down a column & enter 1,2,etc in th
column to the right. (I used cells E2 to F13 in may example)

2) Enter the below formula into cell D2 & copy it down for as many row
as needed:

=DATE(RIGHT(B2,5),VLOOKUP(MID(B2,FIND(" ",B2,FIND(
",B2,1)+1)+1,(LEN(B2)-5)-FIND(" ",B2,FIND(
",B2,1)+1)),$E$2:$F$13,2,FALSE),MID(B2,FIND(" ",B2,1),FIND(
",B2,FIND(" ",B2,1)+1)-FIND(" ",B2,1)))

3) Format column D as you want it, select all data & sort by column D.

I created this formula by developing the month, day, & year formula
separately and then merging them into one larger formula. You will nee
to change "$E$2:$F$13" if your lookup table is in a different locatio
to mine.

Hth
Rob Brockett
NZ
Always learning & the best way to learn is the experience..

 

[derksj]

hi

I want to sort the datum collum (the middle collum)
ofcourse the other collums must change when the sorting takes place

greetings Jurge

 

[robert111]

Now I understand

in one cell put =search("y",yourcellreference)+2 (say it is in cel
T2)

this finds the position of the letter y and adds 2 to it

in another cell put =mid(yourcellreference,T2,50)

this gives you the date but in text format (say it is in cell U2)

in another cell put =value(U2) and format this cell to the require
date format

copy paste special values to all these new dates and delete the othe
columns, give the new column a heading and now sort as normal