I have a routine where a column with integer values is formatted as dates.
When the value can't be displayed as date, for example something like
18500618, it will be displayed in the
sheet as hashes. I need to check for this happening and do a conversion. I
have tried to do this check on the
actual value in the cell, but that doesn't always work, so I would like to
check for the displayed hashes.
This now is not that easy. Just checking for the character # isn't
foolproof.
Thanks for any advice.

RBS

Hi Bart,

rather then testing for hashes you'd need to check the number itself.

the hashes are just indicating that the cell's formatted output
is too wide for the current column width, but could represent
a valid number or date, AND could represent errors like #N/A!

with following formula you'd check that the value in A2
is a number which, WHEN evaluated/formatted as a date,
would represent a date in this century


=AND(A1=DATEVALUE("1-1-2000"),A1<DATEVALUE("1-1-2100"))

for speed (not clarity) you can change the formula to:
=AND(A1=36526,A1<73051)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

I have a routine where a column with integer values is formatted as
dates. When the value can't be displayed as date, for example
something like 18500618, it will be displayed in the
sheet as hashes. I need to check for this happening and do a
conversion. I have tried to do this check on the
actual value in the cell, but that doesn't always work, so I would
like to check for the displayed hashes.
This now is not that easy. Just checking for the character # isn't
foolproof.
Thanks for any advice.

RBS

You can look for # (in Edit|Find), but look in Values--not Formulas.

In VBA, you could also inspect the .Text property (not the .Value property).

MsgBox Range("a1").Text

RB Smissaert wrote:

I have a routine where a column with integer values is formatted as dates.
When the value can't be displayed as date, for example something like
18500618, it will be displayed in the
sheet as hashes. I need to check for this happening and do a conversion. I
have tried to do this check on the
actual value in the cell, but that doesn't always work, so I would like to
check for the displayed hashes.
This now is not that easy. Just checking for the character # isn't
foolproof.
Thanks for any advice.

RBS

--

Dave Peterson

I've seen this is a continuance of another thread.
pls specify what should happen if the interbase date is "pre 1900"
and i'll post complete conversion code, rather then the error prone
conversion attempted in mentioned thread.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

I have a routine where a column with integer values is formatted as
dates. When the value can't be displayed as date, for example
something like 18500618, it will be displayed in the
sheet as hashes. I need to check for this happening and do a
conversion. I have tried to do this check on the
actual value in the cell, but that doesn't always work, so I would
like to check for the displayed hashes.
This now is not that easy. Just checking for the character # isn't
foolproof.
Thanks for any advice.

RBS

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

I think I now have something that should always (?) work:

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 c.Value2 < 19000000 And c.Value2 10000000 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 c.Value2 < 19000000 And c.Value2 10000000 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


RBS


"keepITcool" wrote in message
...
I've seen this is a continuance of another thread.
pls specify what should happen if the interbase date is "pre 1900"
and i'll post complete conversion code, rather then the error prone
conversion attempted in mentioned thread.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

I have a routine where a column with integer values is formatted as
dates. When the value can't be displayed as date, for example
something like 18500618, it will be displayed in the
sheet as hashes. I need to check for this happening and do a
conversion. I have tried to do this check on the
actual value in the cell, but that doesn't always work, so I would
like to check for the displayed hashes.
This now is not that easy. Just checking for the character # isn't
foolproof.
Thanks for any advice.

RBS

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

KeeoITCool,

Thanks, will give that a try.
Why you think it is better than the code
I gave last?

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

Tried your code, but it didn't work.
It fell over on IsNumeric(c)
After some adjustments I got it to work:


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

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

Dim c As Range

If strFormat = "" Then
For Each c In rng.Cells
Select Case c.Value2
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 = Mid(c, 7, 2) & "/" & _
Mid(c, 5, 2) & "/" & _
Left(c, 4)
End Select
Next
Else
For Each c In rng.Cells
Select Case c.Value2
Case 0 To CLng(#1/1/3000#)
'it's a date, pass
Case 0 To 29993112
c.NumberFormat = "0"
'VBA localizes date. Inserted into Excel as string
c = Format(DateSerial(Left(c, 4), _
Mid(c, 5, 2), _
Right(c, 2)), _
strFormat)
End Select
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".

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

Bart, I see 1 big danger in your code.

the first loop are pure strings. UNFORMATTED by VBA/EXCEL.
your POST 1900 dates are dateserials converted to strings.

if strFormat = "dd/mm/yyyy" that line would produce dates like "15-03-
2004" on my machine. not as YOU anticipate "15/03/2004"


ALSO NOTE:
you set the cell format to "0", but you intend to insert a string.
it would be better:
to set the numberformat to text ( "@" )
OR append an apostrophe to the beginning of the inserted string.


i dont know how many cells you need to process and if the range only
contains these dates..

you could consider using an intermediate array
assuming the array is 'vertical'

dim v,i&

v=r.value2
for i = 1 to ubound(v,1)
v(i,1) = ...convert
next
r.numberformat="0"
r.value2 = v

less overhead because
you write to the range only once, you format the entire range iso cells




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

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

On Sun, 3 Oct 2004 12:47:14 +0100, "RB Smissaert"
wrote:

I have a routine where a column with integer values is formatted as dates.
When the value can't be displayed as date, for example something like
18500618, it will be displayed in the
sheet as hashes. I need to check for this happening and do a conversion. I
have tried to do this check on the
actual value in the cell, but that doesn't always work, so I would like to
check for the displayed hashes.
This now is not that easy. Just checking for the character # isn't
foolproof.
Thanks for any advice.

RBS

It's easier if you keep threads together.

But under what circumstance does the test I posted in my previous response to
you:

==============
If InStr(1, c.Text, "#") = 1 Then
===============

not work?


--ron

I thought I would start a new thread as I would like to narrow it down to
this particular
problem (see if a cell displays the hashes).

But under what circumstance does the test I posted in my previous response
to etc.
I have come across one instance where the cell value was something like
6695, no hashes
were displayed, but the instr function found a hash.
Have no idea how this happened, but I didn't think the instr function was
100% foolproof.

RBS



"Ron Rosenfeld" wrote in message
...
On Sun, 3 Oct 2004 12:47:14 +0100, "RB Smissaert"
wrote:

I have a routine where a column with integer values is formatted as dates.
When the value can't be displayed as date, for example something like
18500618, it will be displayed in the
sheet as hashes. I need to check for this happening and do a conversion. I
have tried to do this check on the
actual value in the cell, but that doesn't always work, so I would like to
check for the displayed hashes.
This now is not that easy. Just checking for the character # isn't
foolproof.
Thanks for any advice.

RBS

It's easier if you keep threads together.

But under what circumstance does the test I posted in my previous response
to
you:

==============
If InStr(1, c.Text, "#") = 1 Then
===============

not work?


--ron

On Sun, 3 Oct 2004 13:39:59 +0100, "RB Smissaert"
wrote:

I have come across one instance where the cell value was something like
6695, no hashes
were displayed, but the instr function found a hash.
Have no idea how this happened, but I didn't think the instr function was
100% foolproof.

RBS

The only way I could get instr to find a hash was if one was displayed in the
cell.

If the cell is date formatted, it can also display a hash if the column is too
narrow.

But you wrote that the cell did NOT display a hash, yet instr found one. I
find that very curious.

Perhaps you can provide enough information to run this down further, as just
putting 6695 in a cell, formatting it as a date, and running the instr test
does not result in instr finding a hash.

If, in fact, the cell is displaying a hash because the cell is too narrow, just
do an autofit before running the instr test.

For example:

=================
Sub foo()
Dim c As Range

Selection.Columns.AutoFit

For Each c In Selection
If InStr(1, c.Text, "#") = 1 Then
MsgBox (c.Text)
End If
Next c

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




--ron

Full agree with you.
Perhaps it was just the column being too narrow.
Haven't been able to reproduce this.

RBS


"Ron Rosenfeld" wrote in message
...
On Sun, 3 Oct 2004 13:39:59 +0100, "RB Smissaert"
wrote:

I have come across one instance where the cell value was something like
6695, no hashes
were displayed, but the instr function found a hash.
Have no idea how this happened, but I didn't think the instr function was
100% foolproof.

RBS

The only way I could get instr to find a hash was if one was displayed in
the
cell.

If the cell is date formatted, it can also display a hash if the column is
too
narrow.

But you wrote that the cell did NOT display a hash, yet instr found one.
I
find that very curious.

Perhaps you can provide enough information to run this down further, as
just
putting 6695 in a cell, formatting it as a date, and running the instr
test
does not result in instr finding a hash.

If, in fact, the cell is displaying a hash because the cell is too narrow,
just
do an autofit before running the instr test.

For example:

=================
Sub foo()
Dim c As Range

Selection.Columns.AutoFit

For Each c In Selection
If InStr(1, c.Text, "#") = 1 Then
MsgBox (c.Text)
End If
Next c

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




--ron