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

 

[Alex Blakenburg]

I've logged off for the night. The a10 and b10 is relative so should work in conditional formatting except in row 1

 

[Alex Blakenburg]

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.

That's different to what was previously understood which referenced being between.
For that would this work for you ?

20240321 Date Between Previous dates jl2509.xlsx
	A	B
1	From	To
2	30/08/2024	29/09/2024
3	5/09/2024	5/10/2024
4	7/09/2024	7/10/2024
5	18/09/2024	18/10/2024
6	20/09/2024	20/10/2024
7	25/09/2024	25/10/2024
8	9/11/2024	9/12/2024
9	18/11/2024	18/12/2024
10	13/12/2024	12/01/2025
11	22/09/2024	28/10/2024
12	13/01/2025	15/01/2025
13	14/01/2025
14
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=AND(A2<>"",A2<=MAX($B$1:$B1))	text	NO

 

[jl2509]

I tried to make it the same outcome but just using the last date rather than between as I mentioned earlier in the thread.
I have also found some flaws in my methodology, apologies for causing any wasted time.

The new formula looks better but I still get the wrong data highlighted.
I have used a set of data now which shows past dates and current/future dates.
The new date in cell A21 shows row 20 as a conflict, but it should be row 19 that is highlighted?

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	13/04/2023	16/04/2023
2	17/04/2023	20/04/2023
3	21/04/2023	24/04/2023
4	25/04/2023	28/04/2023
5	29/04/2023	02/05/2023
6	03/05/2023	06/05/2023
7	07/05/2023	10/05/2023
8	11/05/2023	14/05/2023
9	15/05/2023	18/05/2023
10	19/05/2023	22/05/2023
11	23/05/2023	26/05/2023
12	27/05/2023	01/02/2024
13	01/03/2024	04/03/2024
14	05/03/2024	08/03/2024
15	09/03/2024	12/03/2024
16	13/03/2024	16/03/2024
17	17/03/2024	20/03/2024
18	21/03/2024	24/03/2024
19	25/03/2024	28/03/2024	<-- Should highlight this row as well as the date in A21 conflicts with this row not row 20
20	29/03/2024	01/04/2024
21	26/03/2024
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:B	Expression	=AND($A2<>"",A2<=MAX($B$1:$B1))	text	NO

 

[jl2509]

I think I need to start again in explaining this to stop wasting your time.
Now I look back at the intention of this sheet, I do need to use the between function, otherwise I get the wrong data highlighted. in my example below.
As the date entered in A20 is > all other cells in Col B, nothing should be highlighted.

Book1.xlsx
	A	B
16	13/03/2024	16/03/2024
17	17/03/2024	20/03/2024
18	21/03/2024	24/03/2024
19	25/03/2024	28/03/2024
20	29/03/2024	01/04/2024
21	02/04/2024
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B1000	Expression	=AND($A2<>"",A2<=MAX($B$1:$B1))	text	NO

If the last date entered is between the date in Column A and Col B, then it should highlight them as per the example below. I hope this is much clearer

Book1.xlsx
	A	B
15	09/03/2024	12/03/2024
16	13/03/2024	16/03/2024
17	17/03/2024	20/03/2024
18	21/03/2024	24/03/2024
19	25/03/2024	28/03/2024
20	29/03/2024	01/04/2024
21	26/03/2024
22
Sheet1

 

[jl2509]

I Should also add that this is only applicable to current and future dates, so this example would not look at any dates above row 19, where the date in cell A21 is not between any other dates

 

[Alex Blakenburg]

Applying to a larger range than you need A2:B500, try this:
=COUNT(FILTER($A3:$A$500,($A3:$A$500>=$A2)*($A3:$A$500<=$B2),""))<>0

 

[jl2509]

Thanks again for the support but I am still not getting the desired result!

Using the same data above and the formula you kindly provided I get the wrong cell highlighted as shown below?

Book1.xlsx
	A	B	C
1	13/04/2023	16/04/2023
2	17/04/2023	20/04/2023
3	21/04/2023	24/04/2023
4	25/04/2023	28/04/2023
5	29/04/2023	02/05/2023
6	03/05/2023	06/05/2023
7	07/05/2023	10/05/2023
8	11/05/2023	14/05/2023
9	15/05/2023	18/05/2023
10	19/05/2023	22/05/2023
11	23/05/2023	26/05/2023
12	27/05/2023	01/02/2024
13	01/03/2024	04/03/2024
14	05/03/2024	08/03/2024
15	09/03/2024	12/03/2024
16	13/03/2024	16/03/2024
17	17/03/2024	20/03/2024
18	21/03/2024	24/03/2024	<-- Clash shown here is wrong
19	25/03/2024	28/03/2024	<-- Should be here (new date in Cell A21 is between dates in A18: B18)
20	29/03/2024	01/04/2024
21	26/03/2024		<-- New Date Added
22
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B500	Expression	=COUNT(FILTER($A4:$A$500,($A4:$A$500>=$A3)*($A4:$A$500<=$B3),""))<>0	text	NO

 

[Alex Blakenburg]

Does your data really not have a heading row ? That is a really strange set up.
Your conditional formatting formula is inconsistent with the range.
If your "applied to" range starts at A2 then where you have A3 and B3 it should say A2 and B2.
The range to compare to should start one row down from that so $A3:$A$500 in both spots.

 

[jl2509]

Ok sorted now, not sure how I had changed the formula but adding it back as you proposed works fine. Thanks for the support.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we got there in the end.