Find Newest Date In range defined by Offset

Question

I am trying to find the most recent date based on an offset range. It's kind of hard to explain so here is a screenshot of generic data to help (I couldnt find a file upload):

I created a helper column and date diff. When the helper column hits 0, that indicates the start of the range that needs to be checked. I think using a form of offsets to define the range to be checked will work but I am not sure on how to construct it. It will need to define the range, then get the date from another offset for each line to compare and find the most recent date. It should end up looking like the highlighted rows. Is this even possible?

Hopefully I have explained it adequately.

Thanks!

EDIT: I've been looking at it more and come up with the below formula:

=IF(B2 = SMALL(OFFSET(B2,0,0,2,1), 1),TRUE,FALSE)

Which is half the battle. Next is making sure the height of the offset is covering the range from 0 to the max of the helper column before it hits the next 0.

SECOND EDIT: I've come up with the imperfect solution:

=IF(B2 = SMALL(OFFSET(B2,-C2,0,IF(C3 = 0, C2 + 1,MAX(C2:C5)+1),1), 1),TRUE,FALSE)

Issues with this is the Else statement in the height offset. I know that the MAX in the entire column is a value of 4. So I just set the else to check the next 3 rows it should cover that but it can potentially cause issues.

Also where there are duplicate dates in the range, the SMALL() formula will not pick this up and it will be skipped entirely. Rare, but it happens. If anyone has anything better I am all ears!

Can you please edit your question to clarify what is what: the date diff involves two dates, presumably one is in column A, where is the other? What is the logic behind the helper column? You may want to take the time to read this — cybernetic.nomad
Hi, the helper column just denotes when the new range needs to follow. When it hits 0, that means its the start of the range and it will go to the highest sequential number before it hits 0 again. The Date diff is a simple =TODAY()-Date. Sorry if this wasnt explained well in the original post. Anyways, my imperfect way of doing it was accepted since I ran out of time and it was never my job to do this analysis piece in the first place haha. But if any one can solve the SMALL and HEIGHT issue then that would perfect the formula :) Thanks! — JaayB

JaayB JaayB · Accepted Answer · 2019-03-20T11:07:29

As per my second edit, I came up with something that works but has a few errors and this was accepted for what I needed to do (It was never my job in the first place so it's not like they can complain):

=IF(B2 = SMALL(OFFSET(B2,-C2,0,IF(C3 = 0, C2 + 1,MAX(C2:C5)+1),1), 1),TRUE,FALSE)

If anyone can solve the issues around the SMALL function skipping over values that are identical in the range, and the dynamic height, then you have a solid formula for stuff like this.

Find Newest Date In range defined by Offset

1 Answers