Sorting dates before 1900

[Ken Walter]

I have set up a spreadsheet containing a column that lists nearly 50
dates prior to 1900 in format DD/MM/YYYY. I am trying the sheet o
several columns into date order but it simply orders into DD/MM order
can you help please?

Ken Walte

Attachment filename: ken tree.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52227

 

[Robert Christie]

Ken
This macro assumes your Dates are contigious in Column A
(with no blank cells). Also assumes your Data is 1000 rows
deep and from Column A to M wide.
HTH

Sub Sort_Pre1900_Dates()
'
' Assumes row 1 is header row.
' Assumes dates are on column A without any blank cells.

Application.ScreenUpdating = False

' Format Dates in Column A
Columns("A:A").NumberFormat = "dd/mm/yyyy"

' Selects cell to the right of date column A
Range("B1").Select
' Inserts 4 new columns moving Data to right.
ActiveCell.Resize(1000, 4).Insert
' Select cell 1 row down from activecell.
ActiveCell.Offset(1, 0).Select
' 4 formulas split date into day, month and yeatr columns
ActiveCell.FormulaR1C1 = "=Left(RC[-1],2)"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=LEFT(RC[-
2],5)"
ActiveCell.Offset(0, 2).FormulaR1C1 = "=RIGHT(RC[-
1],2)"
ActiveCell.Offset(0, 3).FormulaR1C1 = "=RIGHT(RC[-
4],4)"

' Fills formula down to last row of data.
Range("B2:E2").AutoFill Destination:=Range("B2:E" &
Range("A65536").End(xlUp).Row)
' Selects all columns of data adjust to suit your data.
Columns("A:M").Select
' Sort in Ascending order by year month and day.
Selection.Sort Key1:=Range("E2"),
Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Key3:=Range("B2"),
Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers,
DataOption2:=xlSortTextAsNumbers, _
DataOption3:=xlSortTextAsNumbers
' Selects and removes 4 columns of formulas, returning
sheet back to normal.
Range("B1").Select
ActiveCell.Resize(1000, 4).Delete

End Sub

Regards Bob C.

 

[Norman Harker]

Hi Ken!

Insert a helper column and use the following formula to convert your
dates to a text format yyyy/mm/dd

=IF(ISNUMBER(B3),TEXT(B3,"yyyy/mm/dd"),RIGHT(B3,4)&"/"&TEXT(LEFT(MID(B3,FIND("/",B3)+1,255),FIND("/",MID(B3,FIND("/",B3)+1,255))-1),"00")&"/"&TEXT(LEFT(B3,FIND("/",B3)-1),"00"))
Copy down.

One trouble is that you have dates after 31-Dec-1899 in your list of
dates. The formula converts these to text in the form yyyy/mm/dd.
Thankfully there are no ordinary numbers in the list of dates.
Another difficulty is your entry of single digit days of month and
month numbers. The formula changes these to (eg) 05.
You have two errors in your date entries: Cells B136 and B346; You'll
need to manually correct those.

You can now sort on the helper column.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

 

[Ian George Haworth]

Ken

Why not split the dates into dd/mm and yyyy into two inserted columns using
the right and left functions. copy these down, highlight the whole data and
then do a data sort on year then by dd/mm. Then delete the two extra
columns.

Ian