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