How to check if a date range I'm adding crosses an existing date range from an ever growing list of date ranges....

[Browz97]

Hello,

I'm using Excel online, so unfortunately no macros...

I am building an absence/leave sheet. It is quite a simple sheet.

Columns are Name(A), Date from (B), Date To (C), Half day(D) Days leave (E) (formula - =IF(ISBLANK(C3),"",NETWORKDAYS(B3,C3))), Name (G), Leave entitlement (H), Remaining days(I) (Formula - =H3-SUMIF(A:A,G3,E:E))

This all works OK.

I am trying to extend it now so if one person requests to take leave which crosses over dates that already exist in my list of date ranges, I am alerted - highlight the row red/put up an alert/basically anything. I've tried using conditional formatting, but haven't been successful.

Anyone able to suggest what I could try next?

Many thanks for reading.

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you give us a bit more sample data and include some rows where the cross over exists and manually highlight them to help show us exactly what you want?

 

[Browz97]

Thanks for your welcome and quick reply Peter!

I've added in a couple of additional rows to show what the clash might look like. The clashing rows are 8 / 9. Row 8 clashes with row 4. Row 9 clashes with row 7.

I've also added in 'Clashes' in column F as this would be a perfect way to highlight the clash.

As more rows are added, there could be more potential clashes.

Does this help?

 

[Peter_SSs]

Thanks for the additional samples/explanations.
Assuming that you have the FILTER function in your version of Excel 365, try this. For clashes, it identifies the person & row of the clashing entry/entries.
Use the column F formula if you want to highlight all clashing entries or column G if you only want to highlight clashes above the row in question like your sample.

Browz97.xlsm
	A	B	C	D	E	F	G
1						Clash-Row	Clash-Row
2
3	Hannah	28/09/2020	29/09/2020
4	Hannah	4/12/2020	11/12/2020			Michael-8
5	Jane	15/10/2020	15/10/2020
6	Jane	30/10/2020	30/10/2020
7	Jane	28/12/2020	31/12/2020			Hannah-9, Tom -10, Jane-11
8	Michael	1/12/2020	8/12/2020			Hannah-4	Hannah-4
9	Hannah	28/12/2020	29/12/2020			Jane-7, Tom -10	Jane-7
10	Tom	29/12/2020	29/12/2020			Jane-7, Hannah-9	Jane-7, Hannah-9
11	Jane	30/12/2020	2/01/2021			Jane-7	Jane-7
12
Crossover Dates

Cell Formulas
Range	Formula
F3:F12	F3	=IF(C3="","",TEXTJOIN(", ",1,FILTER(A$3:A$20&-ROW(A$3:A$20),(B$3:B$20<=C3)*(C$3:C$20>=B3)*(ROW(A$3:A$20)<>ROW(A3)),"")))
G3:G12	G3	=IF(C3="","",TEXTJOIN(", ",1,FILTER(A$3:A3&-ROW(A$3:A3),(B$3:B3<=C3)*(C$3:C3>=B3)*(ROW(A$3:A3)<>ROW(A3)),"")))

 

[Browz97]

Wow. Peter, just wow. That is perfect and works amazingly.

Thank you so much for your incredibly quick response and solution.

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.