Data validation formula

[Gladme]

Hi,

Thank you for your website, and your helps.
I want to write a data validation formula in a few worksheets to allow only entering a date between 1/1/2000 to 1/1/2020 as well as text of "N/A" and "Done" in some cells. Could you please help me?

 

[Joe4]

Welcome to the Board!

I think it gets a bit tricky to do a Data Validation formula that allows different data types (text and dates). I would approach it a different way, and use automated VBA code.
Listed below is code that will do this. To put it in the proper place, go to the sheet you want to apply it to, right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim isect As Range
    Dim cell As Range
    
'   Specify range to apply this to:
    Set myRange = Range("J:J")
    
    Set isect = Intersect(Target, myRange)
    
'   If update not in specified range, exit
    If isect Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
'   Loop through all cells in intersection
    For Each cell In isect
'       Check to see if date
        If IsDate(cell) Then
            If cell < DateSerial(2000, 1, 1) Or cell > DateSerial(2020, 1, 1) Then
                cell.ClearContents
                MsgBox "Date must be between 1/1/2000 and 1/1/2020", vbOKOnly, "DATE ENTRY ERROR"
            End If
        Else
'       Handle text entries
            If (cell <> "N/A") And (cell <> "Done") Then
                cell.ClearContents
                MsgBox "N/A and Done are the only allowable text entries", vbOKOnly, "TEXT ENTRY ERROR"
            End If
        End If
    Next cell

    Application.EnableEvents = True

End Sub

The only thing you should need to change in the code is this line here:
Set myRange = Range("J:J")
Just change the range to the range that you want this to apply to.

 

[Gladme]

Hi Joe4,

Thank you very much for your time and consideration.t works great. I appreciate your kind attention. Just one more question. My range in each worksheet consists of 4 separate range like "D4:G100","J4:J100","M4:N100" and "R4:V100". How can I combine these ranges and address it in code? I mean punctuation I need and texts like "AND" or "Or". Thank you in advance.

 

[Joe4]

Like this:

    Set myRange = Range("D4:G100, J4:J100, M4:N100, R4:V100")

 

[Gladme]

Thank you very much.

 

[Joe4]

You are welcome.