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.

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.

.

In response to the other issue (recognizing date entries
vs. text) - the IsDate function will be true for anything
that is OR can be converted to a date - so the text values
show as true since they are recognized as (text) dates.

The VarType function should work, though:

Dim CheckVal as Variant, CheckValType as Integer

CheckVal = Sheets("DataSheet").Range("CelltoCheck").Value

CheckValType = VarType(CheckVal) ' 7 for date, 8 for String

See VBA help for more info

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

.