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


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

--
derksj
------------------------------------------------------------------------
derksj's Profile: http://www.excelforum.com/member.php...fo&userid=3101
View this thread: http://www.excelforum.com/showthread...hreadid=525608

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=525608

hi

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

greetings Jurgen


--
derksj
------------------------------------------------------------------------
derksj's Profile: http://www.excelforum.com/member.php...fo&userid=3101
View this thread: http://www.excelforum.com/showthread...hreadid=525608

Now I understand

in one cell put =search("y",yourcellreference)+2 (say it is in cell
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 required
date format

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=525608

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

1) Create a lookup table for Months somewhere out of the way eg
January, February, etc in cells down a column & enter 1,2,etc in the
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 rows
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 formulae
separately and then merging them into one larger formula. You will need
to change "$E$2:$F$13" if your lookup table is in a different location
to mine.

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=525608