Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Mask Excel Date Entries

    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!

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    BTW, I am in the KC area as well. Howdy!!
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •