Excel question

[Flying Lawyer]

I'd be grateful for help with an Excel problem.

I've been sent a spreadsheet in which one of the columns is Date of Birth.
The list is currently in alphabetical order by surname.
I need to sort by DoB.

My attempts to do so have been unsuccessful because the DoB format is 04/11/1962 etc and sorting by DoB produces a list starting with people born on the 1st and ending with those born on the 31st.

Is there a way of resolving the problem?
If so, can it be done without having to change each DoB entry individually? (There are almost 2000 names so that isn't practical.)

Thanks.

FL

[Sprogget]

Tudor, you could change the format of the DOB column & try to sort by that column again - I'm sure you know how but if not, highlight the column, format, cells then go from there.

And can I have three other bad answers taken into consideration your honour?:ouch:

[kwachon]

Take a look at this chat log. I used some of the methods and it worked fine for me.

Sorting Dates in Excel [Archive] - PC World Forums (http://forums.pcworld.co.nz/archive/index.php/t-31638.html)

Cheers

KW:ok:

[Flying Lawyer]

Success!

Thanks for the suggestions.

I adapted an idea posted on Kwachon's link and came up with what turned out to be a very easy solution.
From frustration to solution in less than 40 minutes. Isn't PPRuNe wonderful. :ok:

In case the question is asked by someone in the future, I did this:

Add a column
Insert the formula =YEAR(G2) where G is the Date column
Block copy the formula down the entire new column.
Sort by the new column.That formula obviously copies only the year, so sorting by the new column leaves days/months out of sequence but I don't need them in chronological order for my purposes, just the years.

[Feline]

So nice to see you on Pprune again Tudor! Even in this somewhat obscure forum (!?)

Sprogget is on the right track - First check for consistency in the date format (ie. make sure that there aren't any inconsistencies in the separators);

Then highlight the column containing the dates and then click Format => Cells; Select Custom (right down at the bottom) and enter yyyy/mm/dd, Click OK -- and that should do the trick ...

Feline Felicitations

Aaah! See you got there before me - but my way doesn't call for inserting a new column - more elegant?

[seacue]

IIRC, the SI encouraged date format is yyyymmdd, which collates correctly as a number, most significant digit on the left. Doesn't even have to be thought of as a date. I think the Japanese tend to write dates that way in ordinary use.

[Sprogget]

http://i40.tinypic.com/2agvkgi.jpg


Yeah, this the thing I use at work, the date order is the key sort. If it didn't sort correctly, I would be at risk of not dealing with a job in time, so I strictly format the date & strictly sort the whole sheet by the collection date and then the delivery date in that order.

I'm good at sorting by date.;)

[Flying Lawyer]

Feline

Thanks for the warm welcome. I still read PPRuNe from time to time but, now that I'm so restricted in what I can say in the discussions I enjoyed most, not as often.

Your method would indeed be more elegant but, for some reason, the date column in the particular spreadsheet I'm using refuses to reformat - whether I use Date or Custom.
I'd tried it when Sproggett very helpfully set me on that track, but with the same lack of success.

I've no idea why. I've tried using the same method to reformat the date column in other Excel spreadsheets from the same source and it works every time.


BTW .....
I see you work in computers in Africa.
Did you and I have a mutual interest in Ealing some years ago?
Just a long shot.

[Sprogget]

Just a quick thought Tudor, you could of course save a copy of the document under a different file name & mess about with it to your hearts content until you absolutely nailed it, safe in the knowledge that you're not damaging the original data.

[Feline]

Dear Tudor

Yes -- I do see what you mean. I rather think that this is down to the fact that whoever inputted the data in the first place simply typed the date into the formula bar without first formatting all the cells in the column to be in date format - so Excel thinks that the contents are text.
There is a function within Excel called DATEVALUE which converts a date in text (eg. 03/12/1940) into an Excel date;
Usage is: =DATEVALUE("03-December-1940") (it doesn't seem to understand "03-12-1940")
That seems to do the trick - you might want to play around with that (my Excel skills are such that I personally would paste the whole date column into a one column table in a word document, then do some multiple find and replace operations, and then paste it back into the spreadsheet).
If you're happy with just getting everything into years, then I would suggest you stick with what you have achieved so far. If you would really have it in the correct order within each year (Yeh! I can be a real purist at times!), then you might want to play around with the DATEVALUE function ...

Don't think I'm your man from Ealing - I won an RAF scholarship to (as it was then) RAF Henlow (the whole Biggin Hill => Cranwell selection process), then lost it three months later for medical reasons. So I got myself into the 23rd SAS (lots of unlikely fun) but decided that a career in electronics (and then computers) offered a whole lot more longevity. Then took a BOAC VC10 to South Africa - and never really made it back to the UK on a permanent basis. Such is life! If I hadn't lost the RAF Scholarship I suspect that I would have been a contemporary of BEagle ... But there you go ...

Feline Felicitations ...

[Flying Lawyer]

Sproggett

Thanks.
My knowledge of Excel is very basic so I took that precaution in case I messed up the list irretrievably.


Feline
Thanks also.
I'll try your suggestions in due course. It would be good to learn more about Excel's features.

The other question was just a long shot because I knew someone who started in aviation, changed to computers and then emigrated to SA.
Coinicidences do happen. You say you would have been a contemporary of BEagle's. He and I were contemporaries on the London University Air Squadron (many) years ago.

[BEagle]

Objection!

London University Air Squadron?

'tis the University of London Air Squadron, or ULAS. Not to be confused with LUAS, an organisation of far less nobility based up in ScouseLand, many of whose graduates have undoubtedly stood quivering miserably in His Honour's dock on occasion:

http://i14.photobucket.com/albums/a341/nw969/BGT.jpg

LUAS members compare their techniques for stealing VCRs.

Tudor, I was at WW last Saturday. The WLAC part hasn't changed much - but the ex-RAF part is virtually unrecognisable now. Well, it was nearly 40 :eek: years ago!

[bnt]

Another way I've used before when data format is uncertain:
- export the data to a Comma-Delimited Text file
- import it in to a new spreadsheet. The import wizard asks you to confirm what's in each column, so you can specify that column is a mm/dd/yyyy date.