format cells with ### as number, then convert to string showing date.

[RB Smissaert]

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:

Sub ChangeHashesToDates(ByRef rng As Range, Optional ByVal strFormat As
String = "")

Dim c As Range

Application.StatusBar = _
" Converting pre Excel dates, please wait ..."

If strFormat = "" Then
For Each c In rng
If Len(c.Text) = 32 Then
c.NumberFormat = "0"
c.Text = Right(c.Value, 2) & "/" & _
Mid(c.Value, 5, 2) & "/" & _
Left(c.Value, 4)
End If
Next
Else
For Each c In rng
If Len(c.Text) = 32 Then
c.NumberFormat = "0"
c = Format(DateSerial(Left(c, 4), _
Mid(c, 5, 2), _
Right(c, 2)), _
strFormat)
End If
Next
End If

End Sub


Thanks for any advice.

 

[Ron Rosenfeld]

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

 

[RB Smissaert]

Thanks, will try that.
My situation is actually somewhat different in that the dates are either in
the yyyymmdd format (the pre-1900 dates) or in the Excel integer date format
(the post 1900 dates).
Still, there are some new ideas.

RBS

 

[Ron Rosenfeld]

Thanks, will try that.
My situation is actually somewhat different in that the dates are either in
the yyyymmdd format (the pre-1900 dates) or in the Excel integer date format
(the post 1900 dates).
Still, there are some new ideas.

Well, it will take a bit longer to do that comparison but, again, assuming the
cell range is formatted as date; and the pre 1900 dates are in the format of
yyyymmdd then, to add a test for '#' and only change those that are that way:

======================
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
If InStr(1, c.Text, "#") = 1 Then
yr = Int(c.Value2 / 10 ^ 4)
mnth = Int(c.Value2 / 100) Mod 100
dy = c.Value2 Mod 100
c.Value = Str(DateSerial(yr, mnth, dy))
End If
Next c

End Sub
======================

takes about 1.7 sec on 10,000 cells on my machine.

However, there is some ambiguity between Excel dates and the pre-1900 dates
formatted the way you describe.

For example, a date prior to 1 January 296 would be ambiguous.

12/31/295 would, in the yyyymmdd format be 02951231 which, in the Excel date
format would be 3/11/9980.

This may or may not be a problem depending on your range of data )



--ron

 

[RB Smissaert]

This is no problem with my range of data, but your code is now the same as
the one I had in
the first place, except for the line:
c.NumberFormat = "0"
which is needed.
I think this is probably as fast as it can be done and it is fast enough.

RBS