sorting dates in excel

R

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
 
B

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..
 
D

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top