Require specific Date format in cell

Question

Hi,

I know this topic has been covered before, but I didn't see a solution that addressed my problem. 

I would like to force users to enter a valid date in format mm-dd-yyyy into a cell.  I have formatted the cell with custom format mm-dd-yyyy, and this successfully converts the entered data to the correct format.  However, it allows any number to be entered, and will subsequently convert it to what it thinks is the right date.  How can I make sure only a valid date is entered, along with having it formatted per my conditions?

Thanks!

Scott

Moved from: (Office | Excel | Windows other | Office 2010)

Answer 1

Thanks OssieMac for your reply.  

However, I was able to accomplish what I needed with the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim exp As Range

    Set exp = Me.Range("C8")

    If exp > "" And exp.Value <> "N/A" Then 

    If Not IsDate(exp) Then

        MsgBox "Must be a Date in format mm-dd-yyyy"

        exp.ClearContents

        exp.Select

        Exit Sub

    ElseIf Len(exp) <> 10 Then

        MsgBox "Date must be in format mm-dd-yyyy"

        exp.ClearContents

        exp.Select

        Exit Sub

    ElseIf InStr(exp.Value, "/") > 0 Then

        MsgBox "Date must be in format mm-dd-yyyy"

        exp.ClearContents

        exp.Select

        Exit Sub

    End If

    End If

End Sub

I hope that this might help others!

Answer 2

I really need all of the rules as to how you will know if the date is inserted in the correct format and what range of dates is valid. Is the method of using a list of dates not acceptable because that is the best way I know of ensuring a valid date entry.

My reason for using Min and Max date is that ensures that it is a date within the correct ball park and if number format is used then it should convert to a date within that range.

With a TextBox, the entry is always in text format and to use as a date, it needs to be converted from text to date format. With Text, it is fairly easy to test the entry with VBA code. Possibly you can format the range as Text so that the date is entered as text.

Then use similar code to what you have used for the TextBox to validate the entry (or my example below)

If valid then use the VBA to then reformat the individual cell to mm-dd-yyyy.

Insert the date as a date.

The problem with the above method is that the cell is then in date format and the validation code will not work if the entry is changed again. However, see my comments where you can simply leave the entry in text format then to use as a date, convert from text to date with the DateValue function anywhere that it is required.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim arrSplit As Variant

    Dim bInvalid As Boolean

    Dim dteValid As Date

    Dim strMsge As String

    Dim lngMaxDay As Long

    On Error GoTo InvalidMsge       'To ensure that EnableEvents is turned on again.

    If Target.Column <> 1 Then Exit Sub

    If Target.Cells.Count <> 1 Then Exit Sub

    If Target.Value = "" Then Exit Sub

    Application.EnableEvents = False

    If Len(Target.Value) <> 10 Then    'Wrong number of characters

        bInvalid = True

        strMsge = "Invalid number of characters in date." & vbCrLf & _

                    "Please re-enter in mm-dd-yyyy format."

        GoTo InvalidMsge:

    End If

    arrSplit = Split(Target, "-")

    If UBound(arrSplit) <> 2 Then   'Not equal to 2 then hyphens not used

        bInvalid = True

        strMsge = "Invalid separators in date." & vbCrLf & _

                    "Please re-enter in mm-dd-yyyy format."

        GoTo InvalidMsge:

    End If

    If arrSplit(0) < 1 Or arrSplit(0) > 12 Then

        bInvalid = True

        strMsge = "Error! Invalid Month." & vbCrLf & _

                    "Please re-enter in mm-dd-yyyy format."

        GoTo InvalidMsge

    End If

    'If Month is valid then could test the day for the specific month

    Select Case arrSplit(0)

        Case 9, 4, 6, 11

            lngMaxDay = 30

        Case 1, 3, 5, 7, 8, 10, 12

            lngMaxDay = 31

        Case 2

            'Centaury years that are not leap years not included because unlikely to be a problem

            If arrSplit(2) Mod 4 = 0 Then

                lngMaxDay = 29

            Else

                lngMaxDay = 28

            End If

    End Select

    If arrSplit(1) > 0 And arrSplit(1) <= lngMaxDay Then

        dteValid = DateSerial(arrSplit(2), arrSplit(0), arrSplit(1))

    Else

        bInvalid = True

        strMsge = "Error! Incorrect days for month." & vbCrLf & _

                    "Please re-enter in mm-dd-yyyy format."

        GoTo InvalidMsge

    End If

    Target.NumberFormat = "mm-dd-yyyy"     'Delete or comment out if using following Alternative method

    Target.Value = dteValid                'Delete or comment out if using following Alternative method

    'Alternative code to above is to not re-format the cell and

    'not write the date back to the cell and leave the entry in text format.

InvalidMsge:

    If bInvalid Then

        MsgBox strMsge

        Target.Select

    End If

    If Err.Number <> 0 Then

        MsgBox "An error occurred in Module " & Me.Name & " Private Sub Worksheet_Change"

    End If

   Application.EnableEvents = True

End Sub

Answer 3

Hi Ossie, please ignore my last response. 

What I meant to say is...

I have found that using data validation as you suggested, still allows them to enter any number, because my formatting automatically converts it to a date in mm-dd-yyyy format.  If I only use your data validation, then I don't get the format control.  I need to have both.   I need to make sure it's a date, but also, that the date is in format mm-dd-yyyy. 

I have done this before with VBA using a textbox, but it doesn't seem to work for just a cell value.

Thanks again!

Answer 4

Thanks Ossie, but I have found that using data validation as you suggested, simply converts any number they enter into a date.  I am trying to force a valid date in the format mm-dd-yyyy.  Thanks again!

Answer 5

1. Select the cell
2. Select Data ribbon
3. Select Data validation -> Data validation
4. Click the DropDown at the right of the field displaying "Any value
5. Select "Date" from the DropDown
6. I assume that the data to be between dates as per the default option but click the DropDown and select different option if required.
7. If "Between" selected in previous step, Enter the Start and End dates in you regional date format.
8. Click OK to finish.
9. Note: You can use valid formulas for both the start and end dates. Requires leading equal sign

Formula Examples:

Start:  =TODAY()

End:    =TODAY()+30

• You can also set up a list of valid dates somewhere out of the way on your worksheet or even another worksheet.
• Then at step 5 select "List".
• Then at the Source field, click the up pointing arrow at the right of the field and then select the range with the list (including changing worksheets if necessary) and once selected, click the icon at the right again to get back to the dialog and then click OK.

Hope this resolves your problem.