Identify Invalid Dates
Posted by JAF on July 10, 2000 11:04 AM
Problem: I have a text file which contains date field data taken from our mainframe system. On the mainframe, dates are stored (or at least should be stored) as yyyymmdd format - so that 10th July 2000 would be 20000710.
What I need to do is to identify any invalid dates that may have been previously input eg 19990431 would be invalid as there are only 30 days in April.
I have managed to this with a macro that inserts a blank column and puts in the following for each cell in the selection:
If cell.Value = 0 Then
cell.Offset(0, 1).Value = 0
cell.Offset(0, 1).NumberFormat = "0"
Else
cell.Offset(0, 1).FormulaR1C1 = "=IF(RC[-1]=0,0,CONCATENATE(RIGHT(RC[-1],2),""/"",MID(RC[-1],5,2),""/"",LEFT(RC[-1],4)))*1"
cell.Offset(0, 1).NumberFormat = "dd/mm/yyyy"
End If
The above turns the text string into a date (dd/mm/yyyy) and returns a #VALUE error message whenever an "invalid" date is encountered.
Ideally, I'd like to end up with the same result without having to insert the "spare" column to hold the formula. Any suggestions?
JAF
Posted by Ryan on July 10, 0100 5:25 PM
Hey JAF,
I didn't know what you wanted to do once you found an invalid date so I just poped up a message box saying it was invalid. You can do whatever you need w/ it. Hope it helps, let me know.
Ryan
Sub Dates()
On Error Resume Next
Dim CellDate As Date
Dim CheckDate As Date
If ActiveCell.Value = 0 Then
ActiveCell.Offset(0, 1).Value = 0
ActiveCell.Offset(0, 1).NumberFormat = "0"
Else
CellDate = Mid(ActiveCell.Value, 5, 2) & "/" & Right(ActiveCell.Value, 2) & "/" & Left(ActiveCell.Value, 4)
CheckDate = CellDate + 1
If Err <> 0 Then
MsgBox "Cell " & ActiveCell.Address(False, False) & " is invalid. ", vbCritical, "Error"
Err = 0
End If
End If
End Sub
