excel sorting dates from mainframe system

[stoke]

interesting one
i have following data from a mainframe system representing dates as (d)dmmyy.
70303
40303
120303
200303
60203
190303
260303
This file has 10000 entries, i need to be able to sort the data in date order.
Formatting the data as date doesn't work as it thinks the values are date values.
And if i sort the data it looks at the first figure so will give:
40303
60203
70303 etc.
I guess i need to add leading zeros to those dates that need it and somehow seperate the data to read dd-mm-yy.

any help or ideas would be greatly appreciated!
Thank you,

 

[PaulTEG]

It's interesting, I'll give you that, the data you supplied has Non breaking spaces appended to it, which make sthe length of the trimmed string still = 9 as opposed to 6 or 5.

In the raw data, are these  's still apparent, or was that your formatting

Paul

 

[PeterMoran]

Hi,

I would approach your problem in two stages, on the basis that you need the data in yymmdd format, actually it will be ymmdd if your dates are all year 2000 or later as per your sample (if not then some minor further work will be required):

1. Assuming your data is in col A, then in Col B enter the formula =TRIM(A1) and copy down the whole list. This removes blanks from the data, so what we see is what we have.

2. In col C I would place the following formula:

=VALUE(IF(LEN(B1)=5,RIGHT(B1,2)&MID(B1,2,2)&"0"&LEFT(B1,1),RIGHT(B1,2)&MID(B1,2,2)&LEFT(B1,2)))

This changes the data to YYMMDD order and leaves it as a value ready to sort - the missing leading zero will not matter if all the data is years 01 - 03. If not then remove the VALUE(.....) from the formula and you will get 6 characters, but text rather than numbers - which may be quite OK.

Let me know if you have a further problem.

Good Luck!
Peter Moran
Two Heads are always better than one!

 

[xlbo]

As a sliight note - you will need to format the cells as date or they will just show as the date serial number and whilst I agree with Peter's methodology - I disagree about needing yymmdd - you have specified ddmmyy so here's a slight adjustment of Peter's formula to do that:
=VALUE(IF(LEN(B1)=5,LEFT(B1,1)&"/"&MID(B1,2,2)&"/"&RIGHT(B1,2),LEFT(B1,2))&"/"&MID(B1,2,2)&"/"&RIGHT(B1,2))

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[TonyJollans]

Assuming you do have space that you can TRIM would it not be easier to to use =RIGHT("0"&TRIM(A1),6) as the first step and then you avoid using IF in the second step so Geoff's formula can just become:

=LEFT(B1,2)&"/"&MID(B1,2,2)&"/"&RIGHT(B1,2)

Enjoy,
Tony

 

[PeterMoran]

Hi all,

From what I can see we have barely one working solution to this problem so far - I provided a sortable date field in the format yymmdd which worked except for my 2nd MID which needs to be modified so that the formula is such:

=VALUE(IF(LEN(B1)=5,RIGHT(B1,2)&MID(B1,2,2)&"0"&LEFT(B1,1),RIGHT(B1,2)&MID(B1,3,2)&LEFT(B1,2)))

I agree with Geoff that it was not in DDMMYY format and was not an Excel date.

Geoff - I was not able to get your formula to work, at least in Excel 2000 - it needed to be changed so that the VALUE functions are associated with the dates, not on the outside:

=IF(LEN(B1)=5,VALUE(LEFT(B1,1)&"/"&MID(B1,2,2)&"/"&RIGHT(B1,2)),VALUE(LEFT(B1,2))&"/"&MID(B1,3,2)&"/"&RIGHT(B1,2))

Also the result is the day number which then needs to be formatted to be seen as the date, although this is not necessary for sorting in date sequence.

Tony - I liked your first formula but your second formula needed the MID altered like mine to MID(B1,3,2) and also needed to have the VALUE function around it to produce a date, which then must then be formatted as per Geoff's.

Finally I have also found that TRIM only works for the space character (ASCII code 32), and if I copied and pasted the data directly from the web TRIM did not remove the blank characters which were ASCII 160. If TRIM does not leave only the digits then I can assist further.

I hope this assists in achieving a working solution, and I hope I have not got on the wrong side of either Geoff or Tony.

Good Luck

Peter Moran
Two heads are alwyas better than one!

 

[xlbo]

No worries Peter - looks like we're more interested in this than Stoke ;-)

I have to admit, all I did was copy your formula and changed the order (naughty Geoff - really should check my formulae 1st)

FYI, you are correct regarding TRIM - it is specifically designed to remove spaces only (char32). However, you can use the CLEAN function which should remove any non-printable characters (ie web mess and other stuff)

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[TonyJollans]

All's fine with me Peter,

You're absolutely right in your observations. I replied quickly this morning before I went out and, instead of doing a proper job just cut and pasted the bits of Geoff's formula I wanted (I didn't have to scroll so far up the screen for Geoff's as I would have had to have done for yours!)

We'll just have to wait and see if any of this helps stoke.

Enjoy,
Tony

 

[KenWright]

This thread's better than the telly tonight, so what the hell - Just to give him a bit more choice. Assuming he has cleaned his data should it need it with something like Dave McRitchies great Trimall macro:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

and further assuming that his data was all post 2000, the OP could then just use the following

=DATE(20&RIGHT(A1,2),MID(TEXT(A1,"000000&quot,3,2),LEFT(TEXT(A1,"000000&quot,2))

If not all post 2000 then mine gets a fair bit longer:-

=IF(--RIGHT(A1,2)<30,DATE(20&RIGHT(A1,2),MID(TEXT(A1,&quot;000000&quot,3,2),LEFT(TEXT(A1,&quot;000000&quot,2)),DATE(RIGHT(A1,2),MID(TEXT(A1,&quot;000000&quot,3,2),LEFT(TEXT(A1,&quot;000000&quot,2)))

Regards
Ken.................

 

[PeterMoran]

Hi all,

Sure hope this helps stoke.

Ken thanks to you and Dave McRitchie for Trimall it does the job as you indicated and is now in my personal.xls and on my Macros menu item.

For what its worth here is an array formula from Bob Ulmas (

http://www.emailoffice.com/excel/arrays-bobumlas.html)

which does the same job:

=1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$10),1)),0),SUM(IF(ISNUMBER(1*MID(A1,ROW($1:$20),1)),1)))

Array formulas are entered with Ctrl+Shift+Enter.

Good Luck

Peter Moran
Two heads are always better than one!

 

[stoke]

Wow!
Guys, what can i say?
Many thanks for all your efforts on this.
Geoff, Peter and Ken your sloutions all worked and Tony yours did with a tiny tiny amendment to:
=LEFT(B19,2)&&quot;/&quot;&MID(B19,3,2)&&quot;/&quot;&RIGHT(B19,2)

This is something of a humbling experience, firstly because of your collective knowledge and secondly becuase of your enthusiasm and willingness to help others in your own time -all of which i am extremely grateful for.

I'm sorry i missed out on all this debate, but i'm in Sydney so was sound asleep for most of this discussion.

Many thanks to you all again!
Stoke.

 

[PeterMoran]

Hi stoke,

Great to see you received and were able to use the benefit of our combined knowledge.

Given I am in Melbourne, I saw your thread around 9:30am Wed, and then put my second input in after midnight, and the third one about 9am today.

Pleased to be able to assist.

Good Luck!

Peter Moran
Two heads are always better than one!