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

Browz97

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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))
Annotation 2020-10-13 122837.jpg

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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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?

Annotation 2020-10-13.jpg
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1Clash-RowClash-Row
2
3Hannah28/09/202029/09/2020  
4Hannah4/12/202011/12/2020Michael-8 
5Jane15/10/202015/10/2020  
6Jane30/10/202030/10/2020  
7Jane28/12/202031/12/2020Hannah-9, Tom -10, Jane-11 
8Michael1/12/20208/12/2020Hannah-4Hannah-4
9Hannah28/12/202029/12/2020Jane-7, Tom -10Jane-7
10Tom29/12/202029/12/2020Jane-7, Hannah-9Jane-7, Hannah-9
11Jane30/12/20202/01/2021Jane-7Jane-7
12  
Crossover Dates
Cell Formulas
RangeFormula
F3:F12F3=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:G12G3=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)),"")))
 
Solution

Browz97

New Member
Joined
Oct 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Wow. Peter, just wow. That is perfect and works amazingly.

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You are very welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,088
Messages
5,628,599
Members
416,327
Latest member
Chimay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top