Output a Number Based on Depth and Date when there are Overlapping Dates and Depths

I have multiple sheets with overlapping dates and times, I need to output a number corresponding with which "BHA" is being used.
Please see example sheet with more explanation and current formula.

I have it mostly figured out, but its a messy bunch of nested if statements. Can anyone help clean up the nested formula?
Nested formula must have been too long, it wouldn't let me wrap code or formula tags around it...I uploaded the entire workbook, the formula is in T13 on the "Slide Sheet" tab

First, when 723 is incurred at first workbook, what BHA you want to choose?

Second, as you split the sheet by when the result is overlap, why don't your formula does not include them?
(ie, In first workbook, for the vlookup formula, Slidesheet use E3:G4 instead of E3:G8, slidetrack use E5:G6 etc...)

For the date and time portion, it is better to add in at beside the AL:AN, it make the formula more simple.
after that, you can also try conditional formatting,
conditional formatting > New Rules... > use a formula to determine which cells to format

Formula:

Please Login or Register  to view this content.

it compare the date to the end date, based on the BHA#.

although this this might not your want, but I hope it can help you....

Remind that replace the sensitive data to dummy data to avoid unwanted issue.
And a bit Personal experience: instead of convenience or unless it necessary, keep your workbook as simple as you can even split to several files when the workbook is also use by others. When it hard to understand, it slow down the work and easy to make mistake.

sorry forget to attach the workbook...
the formula used in the attachment. A bit of amend,

Formula:

Please Login or Register  to view this content.

because i check your formula, there have iferror, hence, you can change the font color to white. it have same effect to "".
it will check the bha's end date, any date bigger than that will change the format of BHA

PS: this can ignore as the array formula is take care this issue.

The BHA #1 comes out at 723m and BHA #2 goes in at 723. Until B63 BHA #1, then after that BHA#2. See column N for what the output should be.

I not clear here.....(sorry for poor english...)
because your formula is based on column E,
hence the it is important to know when value in column E is 723, it should go to BHA#1 or 2?

I'm not sure what you mean here...The timestamp is in column AK.

I mean in the L1 and L2 in each BHA sheet


ok... finally, try this array formula, because your big file is too complex, I create a new sheet for easier understand,

Formula:

Please Login or Register  to view this content.

this is to check the date, and eliminate the other that not require and left the correct list to lookup.

PS: It will arrange 723 in BHA 2, to avoid this you may add in 0.001(G3 in the sheet 1) at the list.
PS: array formula is need to confirm by Ctrl + Shift + Enter instead of Enter only

If not understand, please follow below step:

1, Paste the formula to the cell
2, Double click on the cell
3, press Ctrl + Shift + Enter
It show { sign in front of formula. (ie {=index(.....)

look it in the attachment.
anyway, i poor in english and explaination, hope you can understand well...

The reason is the start date of BHA 3 and end date of BHA 2 is same.

to fix it, it have to find a value that is different between each batch, it can try to add in the hours and minute in the start and end date.

It main condition is, when a day have 2 BHA (like BHA above),
did the date will overlap between to BHAs?
and not overlap will other BHAs as well?

for example,
BHA 1 end at 1.00pm 15/06/2017
BHA 2 start at 1.01pm 15/06/2017
BHA 3 have data at 1.00pm 15/06/2017

If first two statement above are true, (ie BHA 1and 2).

you can change the formula to
=IFERROR(INDEX($I$3:$I$8,MATCH(B304,IF(($E$3:$E$8<=A304)*($F$3:$F$8>=A304),$G$3:$G$8,""),1)),"")

I just found out some data that is out of scope, (ie. date is belong to BHA 2, but the amount is lesser that the dept in.

what you want to show in cell on this situation?