Sorting by date

[google3luo359]

OK this one is definitely for the experts here.

I have a listing with about 6 columns and 100 rows of data.
I'd like to sort the data by the date column.
The problem is that currently, the date column has the date in the
following text format:

Thu Jun 15-Sat Jun 17
Fri Jun 9-Mon Jun 19, var. concerts/ venues
Sat Jun 23-Sat Aug 26, many concerts


So in addition to being a text field, there is sometimes extra text
added at the end of the field.

Would anyone here be able to tell me how I can sort all of this data
chronologically by date?

TIA Ric

 

[Peo Sjoblom]

You can't, you need to parse out the dates from it but what is more
confusing is that you have 2 dates and excel will never accept that as
numbers
so before anyone can help you parsing it they need to know which of the 2
dates you want to sort by, for instance

Sat Jun 23-Sat Aug 26

how could you ever sort that, it spans 2 months?

Excel will only accept as date

06/23/06 or 08/26/06 (US format) , if you would go for the first date and if
it always start with

weekday space month space day hyphen

then you could use

=--MID(LEFT(A2,FIND("-",A2)-1),5,255)

to get the first date, note you need to format as date or you'll get the
date's serial number





--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[google3luo359]

You can't, you need to parse out the dates from it but what is more
confusing is that you have 2 dates and excel will never accept that as
numbers
so before anyone can help you parsing it they need to know which of the 2
dates you want to sort by, for instance

Yes I realized that I'd have to parse first, I just didn't know how.

... if you would go for the first date and if
it always start with

weekday space month space day hyphen

then you could use

=--MID(LEFT(A2,FIND("-",A2)-1),5,255)

Yes the first date of the two would be fine.
All the dates are pretty uniform in this formatting so it should work.

Now my only problem is that I don't know how to apply the above
formula to parse my data.
What cell do I place the formula in and how do I make it work on my
data?
Also, are there supposed to be those two '--' in front of the MID
expression?
BTW what's the 5, 255 for?

TIA Ric

 

[google3luo359]

Hi Peo,

I figured out how to parse the data with your formula!
Thanks very much. It works very nicely.

I have just one more question. Now that I have parsed the data in a
separate column,
do I just sort all the data by this new column? Will Excel
It's giving now something like:

Jun 15
Aug 9
Jul 21
Aug 10
Jun 23

TIA Ric

 

[google3luo359]

Hi Peo,

As I suspected we're not quite there yet.
I tried to sort based on the new column and ended up with

August 1
August 10
August 22
June 4
June 12
June 22
May 2
May 5
etc.

Obviously I have to fiddle with the dates some more before it will
work.
Any suggestions now?

TIA Ric

 

[Dave Peterson]

Your dates aren't really dates--they're just text.

I'm betting that you didn't use this formula from Peo:

=--MID(LEFT(A2,FIND("-",A2)-1),5,255)

The -- stuff changed the text "dates" into real dates. Excel knows dates by
keeping them numbers. The -- coerces the text that looks like a date into a
number (first a negative number, then it reverses the sign with the second
minus.)

The =mid() function will want to start in the 5th position for 255 characters.
That's just Peo's way of grabbing everything he needs.

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[google3luo359]

Your dates aren't really dates--they're just text.

I'm betting that you didn't use this formula from Peo:

=--MID(LEFT(A2,FIND("-",A2)-1),5,255)

Thanks Dave, you were quite correct with your bet!
I did remove the -- in front of MID thinking it was extraneous.

I tried out the full formula and it works like a charm!
Thanks again and also for the extra reading.

Ric

 

[Dave Peterson]

Peo is very smart. Don't screw with his formulas <vbg>.

(I may not understand them, but I do know that they'll work if I enter them the
way that big Swede tells me!)