How to Data Validate Cell Format to: MM/YEAR - MM/YEAR

[NEW_2VBA]

Happy Friday!
I'm trying to force users to input a date range in the format of MM/YEAR - MM/YEAR into a specific cell (B1).
I tried using Data Validation but I received the "..value doesn't match..", error alert when testing it out.
I selected the Custom option and typed MM/YYYY - MM/YYYY in the Formula section of the Data Validation.

Can anyone help me?

Thanks in advance for any feedback!

 

[Joe4]

A better apporach may be to have two cells where they enter their dates (and use the "Date" option on Data Validation), and then you can build the format you need in another cell, i.e.
If they are entering the dates in cells B1 and C1, then:

=IF(AND(B1>0,C1>0),TEXT(B1,"MM/YYYY") & " - " & TEXT(C1,"MM/YYYY"),"")

Does that work for you?

Another option would be to use some VBA code, specifically a Worksheet_Change event which would fire upon making an entry into a cell.
The code could get a little tricky, as it needs to check for a lot of things.

 

[Joe4]

Here is how you can enter VBA code that behaves like Cell Validation. Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim chkRng As Range
    Dim rng As Range
    Dim cell As Range
    Dim cellAdd As String
    Dim errMsg As String
    Dim m1, m2, y1, y2
    
'   ***ENTER RANGE YOU WANT TO APPLY THIS CODE TO HERE***
    Set chkRng = [COLOR=#ff0000]Range("B1:B100")[/COLOR]
    
'   See if cell updated falls within range
    Set rng = Intersect(Target, chkRng)

'   Exit if no cells updated in that range
    If rng Is Nothing Then Exit Sub

'   Loop through updated cells in indicated range
    For Each cell In rng
        cellAdd = cell.Address(0, 0)
'       See if entry matches requried length
        If (Len(cell) <> 17) Or (Mid(cell, 8, 3) <> " - ") Or (Mid(cell, 3, 1) <> "/") Or (Mid(cell, 13, 1) <> "/") Then
            errMsg = "Entry in cell " & cellAdd & " is not in format 'MM/YYYY - MM/YYYY'"
            Exit For
        End If
'       Get date pieces
        m1 = Left(cell, 2)
        m2 = Mid(cell, 11, 2)
        y1 = Mid(cell, 4, 4)
        y2 = Mid(cell, 14, 4)
'       Check to see if months are valid
        If IsNumeric(m1) And (m1 > 0) And (m1 <= 12) Then
        Else
            errMsg = "Month in first date in cell " & cellAdd & " is not valid or not in format 'MM/YYYY - MM/YYYY'"
            Exit For
        End If
        If IsNumeric(m2) And (m2 > 0) And (m2 <= 12) Then
        Else
            errMsg = "Month in second date in cell " & cellAdd & " is not valid or not in format 'MM/YYYY - MM/YYYY'"
            Exit For
        End If
'       Check to see if months are valid
        If IsNumeric(y1) And (y1 >= 1900) And (y1 <= 2100) Then
        Else
            errMsg = "Year in first date in cell " & cellAdd & " is not valid or not in format 'MM/YYYY - MM/YYYY'"
            Exit For
        End If
        If IsNumeric(y2) And (y2 >= 1900) And (y2 <= 2100) Then
        Else
            errMsg = "Year in second date in cell " & cellAdd & " is not valid or not in format 'MM/YYYY - MM/YYYY'"
            Exit For
        End If
    Next cell

'   Return any errors
    If errMsg <> "" Then
        MsgBox errMsg, vbOKOnly, "ENTRY ERROR!"
        Application.EnableEvents = False
        cell.ClearContents
        Application.EnableEvents = True
    End If

End Sub

This will check to make sure all entries are exactly in format "MM/YYYY - MM/YYYY", as well as making sure then enter valid month numbers (nothing like "13"), and only allow years between 1900 and 2100.

You can easily change the range you wish to apply it to by changing the range highlighted in red in the code.

 

[NEW_2VBA]

Genius! This worked like a charm. Thanks so much for sharing your Excel expertise!

 

[Joe4]

You are welcome.