Sorting pre-1900 dates

[David Kilner]

I have a list of pre-1900 dates which Excel refuses to
sort. Is there a way to make it sort this list into
chronological order (the dates being in dd/mm/yyyy
format) ?
Thanks,
David

 

[filky]

I have a workaround that will do it. I couldn't find a way of mergin
this into a single formulae though.
First create 3 new columns to seperate out the day, month and year
=LEFT(A10,2)
=MID(A10,4,2)
=RIGHT(A10,4)
Then use the DATE function to join them back together again
=DATE(E10,D10,C10)
This will add 1900 years to the year you actually have so that exce
can recognise the date as a valid date (post 1900). Then sort th
whole row (including your original text format) on the new date

 

[Norman Harker]

Hi David!

You need a helper column to get the dates into yyyy/mm/dd format.

You can use the formula:

=RIGHT(A1,4)&MID(A1,3,4)&LEFT(A1,2)

This converts (eg) 14/07/1789 to 1789/07/14