Check if date is valid

[Lava]

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

[Ron Rosenfeld]

On Wed, 19 Oct 2005 03:23:48 -0500, Lava
<[email protected]> 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

[Mike Fogleman]

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" <[email protected]> wrote in message
news:[email protected]...
> On Wed, 19 Oct 2005 03:23:48 -0500, Lava
> <[email protected]> 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

[Lava]

I've spend the rest of the day trying and combining all sorts of code I found and hmmmz... this seems to be working. I think... took some time to get it working nicely. I kept the cell-format as "Text". Any flaws with this code which I overlooked?

Day-Month-Year format.

Input --> Output:
05/06, 5/6, 05/6, 5/06 --> 01-05-2006
05-06, 5-6, 05-6, 5-06 --> 01-05-2006
2006 --> 01-01-2006
02/05/06, 2/5/6 --> 02-05-2006
02-05-06, 2-5-6 --> 02-05-2006

If nothing is inserted it'll turn yellow N/A, if it's wrong it'll turn red and if it's a valid date it'll get the full date notation.

Please Login or Register  to view this content.