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