Multiple data Validations on one cell.

[paun_shotts]

Hi,
I need help with creating multiple data validations in one cell.
I have a date is column B and another date in column J.
I want to apply the data validation to column J with the following criteria.
Date in column J must be less than the date in column B by up to 10 days
Date in column J can only be up to 60 days greater than the date in column B
If the date in column J is not known, allow for the user to input "unknown"

Example:
Column B has a date of 1/1/2022
Date in column J can be between 22/12/2021 and 2/3/22 (up to 10 days prior to 1/1/22 and up to 60 days after 1/1/22)
If the date is not known, allow the user to input "unknown" into the cell in column J

Thank you!!!

 

[bebo021999]

just give a first shot:

 

[paun_shotts]

just give a first shot:
View attachment 71314

Hi,

Yes, this works for the date range, but does not work if I want to put "unknown" in the cell in column J

 

[bebo021999]

Actually, As my knowdledge, add an extra value into existing Data Validation List is not allowed
Try to use helper columns.
If it is not allowed, do you like VBA solution?

Book1
	A	B	C	D						J
1		01-Jan-2022								23-Dec-2021
2
3
4	Unknow
5	22-Dec-2021
6	23-Dec-2021
7	24-Dec-2021
8	25-Dec-2021
9	26-Dec-2021
10	27-Dec-2021
11	28-Dec-2021
12	29-Dec-2021
13	30-Dec-2021
14	31-Dec-2021
15	01-Jan-2022
16	02-Jan-2022
17	03-Jan-2022
18	04-Jan-2022
19	05-Jan-2022
20	06-Jan-2022
21	07-Jan-2022
22	08-Jan-2022
23	09-Jan-2022
24	10-Jan-2022
25	11-Jan-2022
26	12-Jan-2022
27	13-Jan-2022
28	14-Jan-2022
29	15-Jan-2022
30	16-Jan-2022
31	17-Jan-2022
32	18-Jan-2022
33	19-Jan-2022
34	20-Jan-2022
35	21-Jan-2022
36	22-Jan-2022
37	23-Jan-2022
38	24-Jan-2022
39	25-Jan-2022
40	26-Jan-2022
41	27-Jan-2022
42	28-Jan-2022
43	29-Jan-2022
44	30-Jan-2022
45	31-Jan-2022
46	01-Feb-2022
47	02-Feb-2022
48	03-Feb-2022
49	04-Feb-2022
50	05-Feb-2022
51	06-Feb-2022
52	07-Feb-2022
53	08-Feb-2022
54	09-Feb-2022
55	10-Feb-2022
56	11-Feb-2022
57	12-Feb-2022
58	13-Feb-2022
59	14-Feb-2022
60	15-Feb-2022
61	16-Feb-2022
62	17-Feb-2022
63	18-Feb-2022
64	19-Feb-2022
65	20-Feb-2022
66	21-Feb-2022
67	22-Feb-2022
68	23-Feb-2022
69	24-Feb-2022
70	25-Feb-2022
71	26-Feb-2022
72	27-Feb-2022
73	28-Feb-2022
74	01-Mar-2022
Sheet1

Cell Formulas
Range	Formula
A5	A5	=B1-10
A6:A74	A6	=A5+1

Cells with Data Validation
Cell	Allow	Criteria
J1	List	=$A$4:$A$74

 

[Peter_SSs]

You could try this in 'Custom' in Data Validation (for J2)
Formula: =OR(J2="Unknown",AND(J2>=B2-10,J2<B2+60))

 

[paun_shotts]

You could try this in 'Custom' in Data Validation (for J2)
Formula: =OR(J2="Unknown",AND(J2>=B2-10,J2<B2+60))

Thank you! Thats the one!

 

[Peter_SSs]

Thank you! Thats the one!

You're welcome. Thanks for the follow-up.