Recognising dates pre-1900

K

Karen H

I'm a genealogist building a database using Excel 2007, but I can't sort by
dates in chronological order as Excel does not recognise dates pre-1900.
I want to sort by date format: eg 25 Aug 1834, 8 Jan 1732, 30 Sep 1749 etc.

Is there any way of doing this other than reversing the whole date to
18340825.

Any advice appreciated. Thanks
 
G

Gord Dibben

See John Walkenbach's site for an easy to use Add-in.

Many users are surprised to discover that Excel cannot work with dates prior to
the year 1900. The Extended Date Functions add-in (XDate) corrects this
deficiency, and allows you to work with dates in the years 0100 through 9999

http://www.j-walk.com/ss/excel/files/xdate.htm


Gord Dibben MS Excel MVP
 
R

Ron de Bruin

I like to addd this

If you use the ISO date format
http://www.rondebruin.nl/isodate.htm

You can sort

6. If you pre-format a range of cells as text, you can enter all dates as yyyy-mm-dd including dates that
are before 1900-01-01 (Or 1904-01-02 if you use the 1904 Date System).
This allows subsequent sorting of the dates into date order. Without pre-formatting as text, sorting
will sort date serial numbers in date order and (pre-1900) text dates in alphanumeric order.
 
J

James Silverton

Gord wrote on Sun, 20 Apr 2008 11:27:31 -0700:

GD> Many users are surprised to discover that Excel cannot work
GD> with dates prior to the year 1900. The Extended Date
GD> Functions add-in (XDate) corrects this deficiency, and
GD> allows you to work with dates in the years 0100 through
GD> 9999

GD> http://www.j-walk.com/ss/excel/files/xdate.htm

GD> Gord Dibben MS Excel MVP

GD> On Sun, 20 Apr 2008 09:19:03 -0700, Karen H <Karen
GD> [email protected]> wrote:

??>> I'm a genealogist building a database using Excel 2007,
??>> but I can't sort by dates in chronological order as Excel
??>> does not recognise dates pre-1900. I want to sort by date
??>> format: eg 25 Aug 1834, 8 Jan 1732, 30 Sep 1749 etc.
??>>
??>> Is there any way of doing this other than reversing the
??>> whole date to 18340825.
??>>

This is only asked for information; I'm not trying to start a
flame war!

Unix programs can deal with the switch from Julian to Gregorian
("Give us back our 11 days!"), can Xdate?

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not
 
T

Trish

I have printed off the instructions and tried to follow them but when I go to
add-ins there is NO browse button - clicked on manage add-ins buttons then
choose enter add ins clicked on browse - located the file - thought I had
eventually added it in BUT still won't sort. Does date need to be put in
special format? Even Tescos home Office - free with accessories can sort
dates - I am livid - just bought 2007 for more functionality and fallen at
1st hurdle. What is MS playing at!!
Any help greatly appreciated - also for simple family history spreadsheet.
Ugh!!!!

Trish
 
R

Roger Govier

Hi Trish

I don't think that John's addin has been updated for XL2007.

One way around your problem.
Assuming your dates are in column A
In B1 enter
=IF(A1="","",--RIGHT(A1,4))
in C1 enter
=IF(A1="","",MID(A1,FIND(" ",A1)+1,3))
in D1 enter
=IF(A1="","",--LEFT(A1,FIND(" ",A1)-1))
Copy B1:D1 down as far as your data extends
Select columns A:D>Data tab>Sort>Sort by Column B>Smallest to
largest>Add>Sort by column C>A-Z>Add>Sort by column D>Smallest to Largest>OK

You can hide columns B:D after doing your sort.
 
R

Rick Rothstein

I don't think your C1 formula will work correctly in a sort as it will
produce the month names (which are not in alphabetical order). This formula
will produce the month's number in C1...

=IF(A1="","",MONTH("01"&MID(A1,FIND(" ",A1)+1,3)&"2000"))

which could then be used in a numerical sort. However, it might be better to
use only one column instead of three and combine all of your formulas into a
single formula, put it in that column and then use that for performing the
sort. So, instead of using columns B, C and D as you proposed, just put this
formula in B, copy it down, then choose columns A and B and sort on column
B...

=--(RIGHT(A1,4)&TEXT(MONTH("01"&MID(A1,FIND("
",A1)+1,3)&"2000"),"00")&TEXT(LEFT(A1,FIND(" ",A1)-1),"00"))
 
R

Rick Rothstein

In addition to Roger's suggestion (coupled with my comments), you could also
use a macro to switch the format of the dates to a sortable number (the
18340825 format for "25 Aug 1834" that you indicated), sort the data, and
then run the same macro to put the dates back in "readable" form. In the
macro below, just change the three statements that begin with Const to
reflect your actual set up (row containing first date, column letter where
the dates are at, and worksheet name)...

Sub ToggleDateFormat()
Dim X As Long
Dim LastRow As Long
Dim IsNumber As Boolean

Const FirstDateRow As Long = 2
Const DateColumn As String = "A"
Const SheetName As String = "Sheet1"

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row
IsNumber = IsNumeric(.Cells(FirstDateRow, DateColumn).Value)
For X = FirstDateRow To LastRow
With .Cells(X, DateColumn)
If IsNumber Then
Debug.Print Format(.Value, "0000-00-00")
.Value = Format(Format(.Value, "0000-00-00"), "d mmm yyyy")
Else
Debug.Print CDate(.Text)
.Value = Format(CDate(.Text), "yyyymmdd")
End If
End With
Next
End With
End Sub

If you are not familiar with how to install a macro, press Alt+F11 to bring
up the VBA editor, then click Insert/Module from its menu bar and, finally,
copy/paste the code above into the code window that appeared. Now, go back
to your worksheet, press F8, select ToggleDateFormat from the list and click
the Run button. This will change your "dates" to numbers that you can sort
on. When through, do the same thing to run the same macro again and it will
put your dates back to their "readable" form. Since values changed by a
macro cannot be undone, test this all out on a copy of your worksheet.
 
R

Rick Rothstein

Sorry, I left in some debugging code; use this code instead of what I
posted...

Sub ToggleDateFormat()
Dim X As Long
Dim LastRow As Long
Dim IsNumber As Boolean

Const FirstDateRow As Long = 2
Const DateColumn As String = "A"
Const SheetName As String = "Sheet1"

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row
IsNumber = IsNumeric(.Cells(FirstDateRow, DateColumn).Value)
For X = FirstDateRow To LastRow
With .Cells(X, DateColumn)
If IsNumber Then
.Value = Format(Format(.Value, "0000-00-00"), "d mmm yyyy")
Else
.Value = Format(CDate(.Text), "yyyymmdd")
End If
End With
Next
End With
End Sub
 
R

Rod

I have used Rick's code for one of my spreadsheets and it works like a champ
for right now, BUT I need to take it to another level. I have within the
date column dates before 1900, after 1900, and empty that i would like to
sort on. This code just has all kinds of fun with the dates after 1900 and
crashes on the nulls.

Rod
 
R

Rick Rothstein

The following assumes your column of "dates" is Cell Formatted as Text and
should handle all of the problems you described...

Sub ToggleDateFormat()
Dim X As Long
Dim LastRow As Long
Dim IsNumber As Boolean

Const FirstDateRow As Long = 2
Const DateColumn As String = "A"
Const SheetName As String = "Sheet1"

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row
IsNumber = IsNumeric(.Cells(FirstDateRow, DateColumn).Value)
For X = FirstDateRow To LastRow
With .Cells(X, DateColumn)
If IsNumber Or Len(.Text) = 0 Then
.NumberFormat = "@"
.Value = Format(Format(.Value, "0000-00-00"), "d mmm yyyy")
Else
.NumberFormat = "General"
.Value = Format(CDate(.Text), "yyyymmdd")
End If
End With
Next
End With
End Sub
 
Top