MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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"
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?


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.


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"
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