Between dates validation

[jl2509]

Hi All
Is it possible to have a date validated in a range of dates when it is entered.
i.e. a date is entered "Cell A10" in this case and is validated between dates entered in the range A1:B9 but is continually growing in size

The conflicting dates already entered are highlighted YELLOW and the most recent date is highlighted RED

 

[kevin9999]

If I understand you correctly - you want new dates entered in column A to lie between the dates in the row above? If that's the case, try the following data validation - Allow: Custom;
Formula:

=AND(A10>OFFSET(A10,-1,0,1,1),A10<OFFSET(A10,-1,1,1,1),OFFSET(A10,-1,1,1,1)<>"")

I've used A10 as the starting point, but copy this validation to all cells in column A you expect to be using.

 

[Rick Rothstein]

Assuming what @kevin9999 assumed, I believe this formula will also work...

=MEDIAN(A10,OFFSET(A10,-1,),OFFSET(A10,-1,1))=A10

@kevin9999 - do you really need the last OFFSET in your formula?

 

[Alex Blakenburg]

In principle you could use the other arguments of Offset although that makes it far less intuitive.

=MEDIAN(A10,OFFSET(A10,-1,0,1,2))=A10

 

[jl2509]

Hi All
Thanks for he Reponses

I do actually need the last entry in column A to check against the entire table from row 2 as a check for overlap of dates.
I guess this could also take a different approach, where the last date entered in column A (A10 in this case) must be greater than all of the end date(s) in column B?

 

[jl2509]

In case this has not been explained correctly, the list grows daily and could be 10 rows longer each day. I assume that the last row in the responses is not dynamic and fixed at row A10, whereas it would need to be dynamic if possible?

 

[Alex Blakenburg]

Would something like this work for you ?

20240321 Date Between Previous dates jl2509.xlsx
	A	B	C	D
1	30/08/2024	29/09/2024
2	5/09/2024	5/10/2024
3	7/09/2024	7/10/2024
4	18/09/2024	18/10/2024
5	20/09/2024	20/10/2024
6	25/09/2024	25/10/2024	True = No Overlap
7	9/11/2024	9/12/2024
8	18/11/2024	18/12/2024
9	13/12/2024	12/01/2025	Col A Check	Col B Check
10	22/09/2024	28/10/2024	FALSE	TRUE
11	13/01/2025	15/01/2025	TRUE	TRUE
12	29/10/2024	20/11/2024	TRUE	FALSE
Data

Cell Formulas
Range	Formula
C10:D12	C10	=COUNT(FILTER($A$1:$A9,($A$1:$A9<=A10)*($B$1:$B9)>=A10))=0

Cells with Data Validation
Cell	Allow	Criteria
A10:B12	Custom	=COUNT(FILTER($A$1:$A9,($A$1:$A9<=A10)*($B$1:$B9)>=A10))=0

 

[jl2509]

Thanks for the update Alex

Unfortunately this does not work
This still using A10 as a reference point? Today the list is at row 150 and tomorrow will be possibly at row 160
I also cannot add any additional columns as suggested in C and D as data already exists there

As soon as a user enters a new date in column A (the next available empty cell in column A) the conditional formatting formula should check to see if the last date entered is greater than all dates in column B, otherwise highlight the last date entered.

It is the last date entered that needs to be a dynamic reference.

 

[Alex Blakenburg]

Columns C & D were just informational.
Just copy the validation in a10 & b10 down as many rows as you need to eg 1000

 

[jl2509]

Sorry Alex, im lost now.
I am not sure where to add the formula and where is the validation A10 & B10?

Is this formula to be used in conditional formatting or data validation? I am looking for conditional formatting so the dates are highlighted?