sorting dates in excel

  • Thread starter Thread starter derksj
  • Start date Start date
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
 
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..
 
hi

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

greetings Jurge
 
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
 
Back
Top