Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Dec 2015
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,813
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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:
    Code:
    =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.
    Last edited by Joe4; Jul 12th, 2019 at 12:29 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,813
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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:
    Code:
    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 = Range("B1:B100")
        
    '   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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Dec 2015
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,813
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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