i am not sure if thats the best approce to the problem but using that method try this code
[vba]Sub testing()
With Worksheets("Novus DR")
For i = 7 To .Range("AE7").End(xlDown).Row
If Len(.Range("AE" & i)) >= 5 Then
.Range("AE" & i).NumberFormat = "m/d/yyyy"
Else
.Range("AE" & i).NumberFormat = "0"
End If
Next
End With
End Sub[/vba]