Another way would be to control the cell input with Data Validation, leaving
the cell formatted as General. Select Data type as Date, 1/1/1900. Input
message something like "If entering YEAR ONLY, enter as 1/1/year.", error
message "Try Again!". An invalid date will get rejected if they enter
2/29/2005. Since most people will do as they are asked and enter a year as
1/1/year, you have conquered 99.9% of your input validation at the data
entry point. However, that .1% can still enter 2005 and cell validation will
not reject it. But the General format will keep it as 2005 and not try to
convert to a full date. You can then use Ron's method to check for ISDATE or
test for number of characters with LEN. If LEN(cell.value) =< 4, then it has
to be a year only and do your text conversion "01/01/" & cellvalue.

Mike F

"Ron Rosenfeld" wrote in message
...
On Wed, 19 Oct 2005 03:23:48 -0500, Lava
wrote:


I've got an Excel sheet for data-input. For dates it should be possible
to define either a year or a complete date (day-month-year). If one
defines a year it is being converted to 01-01-year. In latter case the
exact date is not known or doesn't matter.

Anyway, for this reason I cannot use the DateFormat for the cells of a
column ("E"), because it would turn 2005 into something like 12-03-1905
or something similar I can't use. I've formatted the cells as Text.

How to check if the entered full date is a valid date?

I can check with CInt(Mid(Cells(i, "E"), 4, 2)) 12 in a For-loop with
parameter 'i' per row and similar If-constructions if the individual day
and month field are not faulty, but 31 April or 29 February (when
there's only 28 days) shouldn't pass the check.

So I'm wondering how I can check the dates? IF it's possible.

Later on the Excel sheet's data will be imported by a PHP script where
all fields are checked again just to be on the safe side and properly
inserted into the database. Question "why bother with Excel-level
checking?". Answer: it's been requested by my employer. If isolated day
/ month checking is as far as I can go... okay, nothing to be done about
that, but IF there's a way to check if a complete date is valid....
please share your thoughts :)

Why not leave column E formatted as General.

If someone enters just a year, that's what they'll see.

If the enter a date, then Excel should parse into a date.

When you check it in your VBA routine, the real dates will return TRUE
with
ISDATE(value). The years will return FALSE and can then be tested to see
if
they are an integer between your acceptable year range for date.


--ron