Date Validation with EITHER of Two Conditions

[ScruffyHuffy]

Hello!

First time posting, so appreciate your help.

I need to restrict input in a cell to EITHER:
1) A Valid Date
2) A short list of text inputs ("Cancelled", "Withdrawn", etc)

Ideas?

Thanks for your help!

Robb

 

[mumps]

There may be a better way but this should work: Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the Range("A1") in the code to suit your needs. Close the code window to return to your sheet. Enter a value in A1 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Not IsDate(Target) And Target <> "Cancelled" And Target <> "Withdrawn" Then
        MsgBox ("You have not entered a valid date or a valid value.  Please try again.")
    End If
End Sub

 

[ScruffyHuffy]

Mumps,

Thanks very much for your quick reply.

I should have clarified. I am not able to save the file as macro-enabled. I know this is restrictive, but it's at management's discretion.

Is there a macro-free approach?

Robb

 

[Scott T]

Without using VBA I am not aware of testing if it is a date. Since dates in Excel are numbers formatted to show as a date you can test if it is a number but then that would allow normal numbers that are not dates. Maybe you could only allow a range of numbers like 42736 to 43465 (1/1/17-12/31/18) adjust to suit your needs.


So if someone entered 7 data validation would not allow it.

=OR(AND(A1>=42736,A1<=43465),A1="Cancelled",A1="Withdrawn")

 

[mumps]

There may be a way by using a formula with Data Validation but unfortunately, that's not my strong suit. Maybe you could start another thread asking for a formula to be used with Data Validation to restrict the entry. Be very detailed in describing what you want to do. Sorry.

 

[Jonmo1]

Validating if an entry is a 'DATE' is not as easy as it sounds.
Data Validation does it pretty well, but as you know you can't do an OR type of thing in validation.
So you have to use a formula.

Dates are difficult to validate because they're really just numbers incrementing by 1 since Jan 1 1900.
1 = Jan 1 1900
43052 = Nov 13 2017 (today is the 43,052nd day since Jan 1 1900)

It's cell formatting that makes those numbers look like dates.
Unfortunately formulas don't look at formatting.

So I think the best you can do is to validate that the value is Numeric (it will not allow Text entries)

So you could select Custom in the Validation, and put the formula
=OR(ISNUMBER(A1),A1="Cancelled",A1="Withdrawn")

That or could be simplified to a match or countif if you put the list of words in a range.
And you could restrict the date a little bit, if you have a range of dates, like no earlier than year 2000, and not beyond year2020 ??

 

[mumps]

It looks like Scott T or Joe4 may have a solution for you.

 

[Fluff]

Have a look at post#2 here & see if it helps
https://www.mrexcel.com/forum/excel...a-validation-date-text-entry.html#post1910895

 

[ScruffyHuffy]

Thanks for all your suggestions. Jonmo1's solution worked perfectly, as did Scott T's.

Thanks again!

Robb

 

[Jonmo1]

You're welcome.