Need help with an Excel date sort

[bigbazza]

I have a column in an Excel spreadsheet that is formatted as dd.mm.yyyy.

I need something that will read this column and output it as yyyy.mm.dd into a separate filed so Excel will sort it properly in chronological sequence.

At present the "date" sort only sorts on the first 2 numerics, ie dd and sorts then from 1-31, irrespective of the balance of the date field.

Hope someone can help.


Bazza

[brownizs]

You would have to do a little work in VB in order to get it to work. I found this http://office.microsoft.com/en-us/as...093431033.aspx

[bigbazza]

Thanks, browizs, for the quick reply. Unfortunately the spreadsheet is a dump from SAP and as such is not in an M$ date format, I believe. I've tried using Format column--> the various date options in Excel, but nothing changes the existing format.

I probably need a little program that will extract the yyyy using something like Right string command, and then get the mm and then the dd using the Left string command.

I am really stretching my memory as I can remember using Left string and Right string in the old DOS Basic days. Bazza

===

Originally Posted by brownizs

You would have to do a little work in VB in order to get it to work. I found this http://office.microsoft.com/en-us/as...093431033.aspx

[bigbazza]

Brownizs, I've tried the example in the link you provided and was able to reverse the dates into 3 columns as yyyy, mm, dd, so will give this a try on the whole worksheet. If anyone comes up with a solution that would transpose the date into one column instead of 3, I would appreciate it. Bazza

==

Originally Posted by brownizs

You would have to do a little work in VB in order to get it to work. I found this http://office.microsoft.com/en-us/as...093431033.aspx

[bigbazza]

Doesn't work with 7.5.2006, but appears to work if I replace 7.5.2006 with 7/5/2006. Will give it a try across the whole worksheet. Bazza

[Mada_Milty]

If you read each line with vb, you could use the split function to break the date into three substrings, each separated by your periods..

What's the Split?
Let's look at the function itself. The Split function, according to the documentation, "returns a zero based, one-dimensional array containing a specified number of substrings." Yes, I can hear the groans; "oh no, not another array function. I don't use no stinkin' arrays in my code anyway!"

Don't give up yet. There is hope. The Split function is defined by this syntax:


Split(expression [, delimiter [, count [, compare]]])

This is how it is explained:

* The function name is Split, and yes, it is a function, so it needs to be on the right hand side of an equal-to sign.
* "Expression" is a required string expression containing substrings and delimiters.
* "Delimiter" is an optional string identifying the delimiter character. By default, a space character (" ") is considered to be the delimiter.
* "Count" is an optional number of substrings to return. The default is -1, which indicates all substrings are to be returned.
* "Compare" is an optional numeric value signifying how the comparison should take place for evaluating substrings. A 0 indicates binary comparison; a 1 (the default) signifies textual comparison.

Given a text string, the Split function can quickly and efficiently break it down into an array of strings, based on your chosen delimiter.

After using this, you would have a day string, a month string, and a year string, and from there, it is EASY to manipulate into the format you like.

[bigbazza]

Thanks Mada_Milty and browizs for your solutions. Somehow, and I'm not sure how I did it, I converted 23.10.2005 to 23-Oct-2005, etc., and was able to get the results I was after. Bazza