On Sat, 2 Oct 2004 17:08:48 +0100, "RB Smissaert"
wrote:

What is the quickest way to make all the cells in defined range have the
number format (NumberFormat = "0") when
they show the hash sign due to an incompatible value with the date format?
These cells have values such as 18950315, which is an Interbase integer date
format.
After the format is changed to number these values will have to be converted
to a string showing the date.
All these values are pre Excel dates, but the problem is that they are
intermixed with normal Excel compatible dates
that are handled in a different way.

I have code that does the job, but it is slightly slow:


I'm never sure what "slightly slow means" and I did not replicate your code,
but the following may give you some ideas.

It assumes a column of dates in the format yyyymmdd with a date range
encompassing both pre-1900 and 1900+ dates (you could easily change the code to
figure out the date system being used).

Since you mention you see "hashes" the code also assumes the column is
formatted as a date.

In any event, it converts all of the entries to VBA dates, which have a greater
range than does Excel. Then if the date is prior to 1900, it outputs the
result as a string; otherwise as a date. On 10,000 cells with a 2GHz 512MB
system, the routine takes about 1.2 seconds to run.

=========================
Sub PreExcelDates()
Dim c As Range
Dim yr As Integer, mnth As Integer, dy As Integer
Dim dt As Variant

For Each c In Selection
yr = Int(c.Value2 / 10 ^ 4)
mnth = Int(c.Value2 / 100) Mod 100
dy = c.Value2 Mod 100
dt = DateSerial(yr, mnth, dy)
If Year(dt) < 1900 Then
c.Value = Str(dt)
Else
c.Value = dt
End If
Next c

End Sub
=====================
--ron