Recognising dates pre-1900
 

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

Recognising dates pre-1900
 

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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

On Sun, 20 Apr 2008 09:19:03 -0700, Karen H <Karen
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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
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

On Sun, 20 Apr 2008 09:19:03 -0700, Karen H <Karen
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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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 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

Recognising dates pre-1900
 

"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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

"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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:DData tabSortSort by Column BSmallest to
largestAddSort by column CA-ZAddSort by column DSmallest to LargestOK

You can hide columns B:D after doing your sort.
--
Regards
Roger Govier

"Trish" wrote in message
...


"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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"))

--
Rick (MVP - Excel)


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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:DData tabSortSort by Column BSmallest to
largestAddSort by column CA-ZAddSort by column DSmallest to
LargestOK

You can hide columns B:D after doing your sort.
--
Regards
Roger Govier

"Trish" wrote in message
...


"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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.

--
Rick (MVP - Excel)


"Trish" wrote in message
...


"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Trish" wrote in message
...


"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

Thanks for catching that for me Rick.
A totally foolish response by me!!!

--
Regards
Roger Govier

"Rick Rothstein" wrote in message
...
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"))

--
Rick (MVP - Excel)


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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:DData tabSortSort by Column BSmallest to
largestAddSort by column CA-ZAddSort by column DSmallest to
LargestOK

You can hide columns B:D after doing your sort.
--
Regards
Roger Govier

"Trish" wrote in message
...


"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Trish" wrote in message
...


"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

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

--
Rick (MVP - Excel)


"Rod" wrote in message
...
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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Trish" wrote in message
...


"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks

Recognising dates pre-1900
 

Rick

Thank you so much that does just what I need it to do.

Rod

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Rod" wrote in message
...
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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Trish" wrote in message
...


"Mike H" wrote:

Karen,

There's an addin here that will allow you to work with dates pre 1900

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

Mike

"Karen H" 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.

Any advice appreciated. Thanks