I often import data from various data sources, and it does
get confusing sometimes trying to get Excel to recognize
dates when they may be imported either as text data or as
a date number.

I find the best thing to do if using VBA is to read the
cell values into a Variant, then treat it as text and
convert it explicitly to a date, e.g.:

Dim CellContents as Variant, ResultDate as Date

CellContents = Sheets("DataSheet").Range("DateRange").Value
ResultDate = DateValue(CellContents)

-----Original Message-----
Hi all,

TIA.
There are some things about Excel which are very
confusing.
In an exported dataset, some cells are dates, but have
been entered in free text and align to the left of the
cell, while other dates in the same column align to the
right.
VBA within a second workbook processes these dates, and i
believe ignored the entries which are aligned to the left.
I have written some code to format the relevant columns
to a standardised date format, but was curious to see if i
could assess what proportion of the data could have
been "ignored".
I have tried IsDate, but it seems to agreee that all
entries are dates.
I tried to query each cell 1/1/1880 - again parsed as
dates.
I tried to cover things by checking that <"" first -
same result.

Is there something i can do to assess these columns ?? I
know its a bit odd that the solution exists and this is a
self-generated issue, but would make for interesting data
integrity stats ;)
Any pointers / links greatly appreciated.

Many thanks.

.