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

1. ## 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.

2. ## Re: Output a Number Based on Depth and Date when there are Overlapping Dates and Depths

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

3. ## Re: Output a Number Based on Depth and Date when there are Overlapping Dates and Depths

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#.

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.

4. ## Re: Output a Number Based on Depth and Date when there are Overlapping Dates and Depths

Thanks for the reply. I removed sensitive data as you reminded me, Thanks!
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.
There won't be an instance where (in the first example workbook) Column B will be the exact depth as the chart E3:G8. Same as real world work book, but column would be E13:E~ and chart would be from AL12:AN26.
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...)
Its because it can vary. This particular job has a "Slide Sheet" and then a "Sidetrack (1)", (2), (3). In this instance, there are 2 BHA's on Slide Sheet (BHA 1 and 2). and on sidetrack (1) there are an additional 2 BHA's (BHA 3 and 4). Then a single BHA on sidetrack 2 (BHA 5) and a single on sidetrack 3 (BHA 6). I don't choose when these will stop and start. some times there will be only one slide sheet and it will have no overlaps and all the BHA's....etc
That's why I don't just alter the start and finish of the "chart"
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
I'm not sure what you mean here...The timestamp is in column AK.
Sorry, I don't understand the Formula, I will play with it some more, but there is nothing in either of the attached workbooks in Column A or C.

5. ## Re: Output a Number Based on Depth and Date when there are Overlapping Dates and Depths

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

1, Paste the formula to the cell
2, Double click on the cell
3, press Ctrl + Shift + Enter

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

6. ## Re: Output a Number Based on Depth and Date when there are Overlapping Dates and Depths

It sort of works... See attachment, I put your Formula in Tab "Slide Sheet" starting in cell N2 and then down to the end.
It seems to work ok until it gets to Cell N207, then it shows BHA #3, but should still show BHA #2.
Do you have a fix for that? The numbers etc I used are the actual numbers from "real world".
Thanks

7. ## Re: Output a Number Based on Depth and Date when there are Overlapping Dates and Depths

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?

8. ## Re: Output a Number Based on Depth and Date when there are Overlapping Dates and Depths

Thanks for your help! I can't get it figured out, so I am going to try a different approach.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1