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 :)


--
Lava
------------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=477441

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

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

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.


Code:
--------------------
Sub CheckDateColumn()
Dim the_cell, converted_Date As String
Dim split_Date
Dim LastRow, i As Long

On Error GoTo DoneChecking

Set wks = Worksheets("Data Schouwing")

With wks
LastRow = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

For i = 2 To LastRow
the_cell = .Cells(i, "E")

If IsDate(the_cell) Then
' It is a date, but well-formatted?

' Only work with dashes, not slashes
the_cell = Replace(Replace(the_cell, "/", "-"), "\", "-")

' Each date should consist of a day, month and year
' Choose day is 01 if it's not specified
split_Date = Split(the_cell, "-")
n = UBound(split_Date) - LBound(split_Date) + 1

If n = 2 Then
the_cell = "01-" & the_cell
End If

'Turn the string in an actual date
converted_Date = CDate(the_cell)

'Format the date and "update" the value of the cell
.Cells(i, "E") = Format$(converted_Date, "dd-mm-yyyy")
.Cells(i, "E").Interior.ColorIndex = 0
Else
' No valid full date, but is it at least a full year?

If Len(the_cell) = 4 Then
If IsNumeric(the_cell) Then
.Cells(i, "E") = "01-01-" & the_cell
.Cells(i, "E").Interior.ColorIndex = 0
End If
ElseIf Len(the_cell) = 0 Or the_cell = "N/A" Then
.Cells(i, "E").Interior.ColorIndex = 6
.Cells(i, "E") = "N/A"
Else
.Cells(i, "E").Interior.ColorIndex = 3
End If

End If

Next

End With
DoneChecking:

End Sub
--------------------


--
Lava
------------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=477441

I've spend the rest of the day trying and combining all sorts of code
found and hmmmz... this seems to be working. I think... took some tim
to get it working nicely. I kept the cell-format as "Text". Any flaw
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 tur
red and if it's a valid date it'll get the full date notation.


Code
-------------------
Sub CheckDateColumn()
Dim the_cell, converted_Date As String
Dim split_Date
Dim LastRow, i As Long

On Error GoTo DoneChecking

Set wks = Worksheets("Data Schouwing")

With wks
LastRow = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

For i = 2 To LastRow
the_cell = .Cells(i, "E")

If IsDate(the_cell) Then
' It is a date, but well-formatted?

' Only work with dashes, not slashes
the_cell = Replace(Replace(the_cell, "/", "-"), "\", "-")

' Each date should consist of a day, month and year
' Choose day is 01 if it's not specified
split_Date = Split(the_cell, "-")
n = UBound(split_Date) - LBound(split_Date) + 1

If n = 2 Then
the_cell = "01-" & the_cell
End If

'Turn the string in an actual date
converted_Date = CDate(the_cell)

'Format the date and "update" the value of the cell
.Cells(i, "E") = Format$(converted_Date, "dd-mm-yyyy")
.Cells(i, "E").Interior.ColorIndex = 0
Else
' No valid full date, but is it at least a full year?

If Len(the_cell) = 4 Then
If IsNumeric(the_cell) Then
.Cells(i, "E") = "01-01-" & the_cell
.Cells(i, "E").Interior.ColorIndex = 0
End If
ElseIf Len(the_cell) = 0 Or the_cell = "N/A" Then
.Cells(i, "E").Interior.ColorIndex = 6
.Cells(i, "E") = "N/A"
Else
.Cells(i, "E").Interior.ColorIndex = 3
End If

End If

Next

End With
DoneChecking:

End Su
-------------------

--
Lav
-----------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...fo&userid=2779
View this thread: http://www.excelforum.com/showthread.php?threadid=47744