formula to lookup count or rank of 100 in list of dates

[bh24524]

Hi, I have a big list of dates in column A and they aren't necessarily falling consecutively, i.e 7/1, 7/2, 7/3. They might actually skip dates like 7/1, 7/5, 7/13. We'll say they start in A1(They don't actually , but for simplicity sake)As you go down the column, you will never find a date that happens prior to the one in the row before. They all fall after one another.

I'm wondering is there is a function or combo I can use to find the 100th date in the list? Let's just say that function will go in B1.

 

[Fluff]

How about

=INDEX(A1:A200,100)

 

[JamesCanale]

I don't expect you are looking for:

B1 formula:

 =A100

So could you describe your problem some more? Are there blanks in your data field?

B1
=index(filter(a:a,a:a<>""),100)

 

[bh24524]

How about

=INDEX(A1:A200,100)

I figured I had to be overthinking this one. In this case, no matter what row the data starts on, it is accurate to say it would be the 90th in the list. The data set will never have duplicates, so I think this function will suit just fine. Thank you, Fluff.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[bh24524]

Okay so it turns out there was an error with the report we are running that has this date data. For some odd reason it is sorting some dates out of order and putting them at the bottom of the page - they are dates that fall prior to some of the other ones. I can sort them and use the above formula, sure, but before I decide that's the process we need to follow, I just want to check and see if by chance there is a formula that can account for that scenario where dates are out of order? Could it be some kind of Rank formula combined with a lookup?

 

[Fluff]

Maybe

=INDEX(SORT(A1:A500),100)

 

[bh24524]

Thank you, that's better. IT won't fix the report, so this at least will help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bh24524]

Sorry I spoke too soon, something is not working right. I went to the actual report this time, the one that I mentioned doesn't start in A1. Here is a screenshot and below it an explanation:

I did a test in the actual report and put the formula in D1: =INDEX(SORT(A13:A200),50) I did a test on a lower count - 50 instead of 100 but it's not accurate. 10/17 is not the 50th date in the list:

It is the 30th. If I change the formula to 70, I get 11/16/23 yielded which although not visible, is 50th in the list. So it is 20 off in both instances. Did I do something wrong in the formula maybe?

I think it has something to do with the years. When I put a 10 in the formula, I am getting a 2024 date and that would definitely not be right.