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