KeepITCool,
Worked this a bit further out by looking at the possible extremes of the
Interbase dates.
This is what I have now and I think this should work in all situations.
I also tweaked it a bit for speed. Seems an If construction is slightly
faster than a Select Case construction.
Also I don't think Mid is faster than Left or Right in this particular Sub.
There is no need for IsNumeric as the data can't be anything else than
integer numerics.
Sub ConvertIBDatesToStrings(ByRef rng As Range, Optional ByVal strFormat As
String = "")
'note that the highest possible Excel integer date is 2958465 or friday
31 dec 9999
'----------------------------------------------------------------------------------
Dim c As Range
Application.StatusBar = _
" Converting pre Excel dates, please wait ..."
If strFormat = "" Then
For Each c In rng.Cells
If c.Value2 = 0 Then
'otherwise this will appear in the sheet as 00/01/1900
'-----------------------------------------------------
c.NumberFormat = "0"
c = 0
Else
If c.Value2 18000100 And _
c.Value2 < 18993113 Then
'the earliest date possible in Synergy is 01/01/1800
'above 31/12/1899 will be handled by Excel
'don't know about S6000
'Interbase date, insert into Excel as string
'---------------------------------------------------
c.NumberFormat = "0"
c = Right(c, 2) & "/" & _
Mid(c, 5, 2) & "/" & _
Left(c, 4)
End If
End If
Next
Else
For Each c In rng.Cells
If c.Value2 = 0 Then
c.NumberFormat = "0"
c = 0
Else
If c.Value2 18000100 And _
c.Value2 < 18993113 Then
c.NumberFormat = "0"
c = Format(DateSerial(Left(c, 4), _
Mid(c, 5, 2), _
Right(c, 2)), _
strFormat)
End If
End If
Next
End If
End Sub
RBS
"keepITcool" wrote in message
...
Bart,
try:
Sub ConvertIBDatesToStrings()
Dim c As Range, r As Range
Set r = Selection
For Each c In r.Cells
If IsNumeric(c) Then
Select Case Val(c)
Case 0 To CLng(#1/1/3000#)
'it's a date, pass
Case 0 To 29993112
'VBA localizes date. Inserted into Excel as string
c.Value = Format( _
DateSerial(Mid(c, 1, 4), Mid(c, 5, 2), Mid(c, 7, 2)), _
"'ddd dd/mm/yyyy")
End Select
End If
Next
End Sub
keepITcool
< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool
"RB Smissaert" wrote:
What should happen when there is a pre 1900 Interbase date (for
example 18500615) is that the integer number gets
converted to a string representing that date with a specified format.
This format is supplied as a string and could be
something like "ddd d/m/yyyy".