Wondering if anyone has had success masking date entries in Excel? My objective is to ensure that '82107', '082107', '8/21/07', '08/21/07' and '08/21/2007' are all formatted/recognized as a date value so that additional calculations can occur following data entry by a user. My knowledge of Excel is limited compared to Access, however, I was hoping to apply formatting/date conversion upon a specified worksheet change. The code below will change a string entered as 082107 to a date value, but then breaks as it loops thru a second time.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim StrVal As String
Dim dDate As Date
Dim int1 As Integer


StrVal = Format(Range("MyDate").Value, "000000")
If int1 = 0 Then
If InStr(StrVal, "/") > 0 Then
Else
If IsNumeric(StrVal) And Len(StrVal) = 6 Then
dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2))

int1 = int1 + 1
Range("MyDate").NumberFormat = "mm/dd/yyyy"
Range("MyDate").Value = dDate

End If
End If
End If
End Sub



I'd appreciate any suggestions as I'm tapped out!

Howdy. I wonder if it might not be easier to set up a user form for data entry. You have much easier/tighter control over the data entered.

Or perhaps even using data validation, which forces the user to enter dates in a specific way. This method means that you don't have to use VBA.

BTW, I am in the KC area as well. Howdy!!

Actually, I am using data validation (custom), but to force the entry of a date = to the current calendar year. The code does the trick on the first pass, but it breaks on the second time thru. I think it's a matter of eliminating the loop. I can give the data form a try in the interim. Thank you.