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]

10/17 is not the 50th date in the list:

That's what the original was doing, the new formula is giving you the 50th date after the dates have been sorted.

 

[bh24524]

That's what the original was doing, the new formula is giving you the 50th date after the dates have been sorted.

But I'm saying, that even though I put 50 in the formula, 10/17/23 is the 30th date in the list, not the 50th. As another example, if I change the 50 to 30 in the formula, I'm getting 9/19/23 as my 30th date and it's the tenth one:

I sorted that list above in order myself just out of curiosity but there wasn't any difference, it was giving the same date.

Could it have something to do with the way the dates are formatted maybe? like how the single digit months have zeros in front of them?

 

[Fluff]

Can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bh24524]

Can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Sure, here you go.

Punch Summary (29).xls
	A	B	C	D	E	F	G	H	I	J	K
1				09/19/2023
2
3
5
6
7
9
11
12	Date	Punch In 1	Punch Out 1	Punch In 2	Punch Out 2	Punch In 3	Punch Out 3	Punch In 4	Punch Out 4	Amount
13	09/12/2023	06:54 AM	03:00 PM						8.10
14	09/13/2023	08:56 AM	05:00 PM						8.07
15	09/14/2023	08:55 AM	05:01 PM						8.10
16	09/15/2023	08:55 AM	04:30 PM						7.58
17	09/18/2023	09:55 AM	06:00 PM						8.08
18	09/19/2023	09:56 AM	04:08 PM						6.20
19	09/20/2023	09:55 AM	06:01 PM						8.10
20	09/21/2023	09:58 AM	05:39 PM						7.68
21	09/22/2023	09:53 AM	05:22 PM						7.48
22	09/24/2023	09:54 AM	05:05 PM						7.18
23	09/26/2023	10:00 AM	04:56 PM						6.93
24	09/28/2023	09:54 AM	04:51 PM						6.95
25	09/29/2023	09:57 AM	05:00 PM						7.05
26	09/30/2023	06:54 AM	12:35 PM						5.68
27	10/01/2023	09:55 AM	05:17 PM						7.37
28	10/03/2023	09:55 AM	04:13 PM						6.30
29	10/05/2023	09:58 AM	06:08 PM						8.17
30	10/06/2023	09:55 AM	05:08 PM						7.22
31	10/07/2023	06:55 AM	01:28 PM						6.55
32	10/08/2023	09:54 AM	06:06 PM						8.20
33	10/10/2023	09:54 AM	04:22 PM						6.47
34	10/12/2023	09:01 AM	04:54 PM						7.88
35	10/13/2023	09:57 AM	06:31 PM						8.57
36	10/15/2023	06:57 AM	06:46 PM						11.82
37	10/16/2023	09:58 AM	06:25 PM						8.45
38	10/17/2023	09:53 AM	05:14 PM						7.35
39	10/19/2023	09:00 AM	04:41 PM						7.68
40	10/20/2023	07:00 AM	12:38 PM						5.63
41	10/22/2023	06:54 AM	01:44 PM						6.83
42	10/23/2023	08:53 AM	05:36 PM						8.72
43	10/24/2023	09:57 AM	04:57 PM						7.00
44	10/26/2023	08:53 AM	04:53 PM						8.00
45	10/27/2023	07:00 AM	06:05 PM						11.08
46	10/29/2023	07:01 AM	01:22 PM						6.35
47	10/30/2023	07:03 AM	05:47 PM						10.73
48	10/31/2023	08:59 AM	04:39 PM						7.67
49	11/01/2023	06:58 AM	04:50 PM						9.87
50	11/02/2023	08:53 AM	06:36 PM						9.72
51	11/05/2023	07:01 AM	07:25 PM						12.40
52	11/06/2023	07:01 AM	06:11 PM						11.17
53	11/07/2023	08:59 AM	01:24 PM						4.42
54	11/08/2023	07:01 AM	06:11 PM						11.17
55	11/09/2023	08:59 AM	06:47 PM						9.80
56	11/12/2023	06:55 AM	07:18 PM						12.38
57	11/13/2023	06:57 AM	06:42 PM						11.75
58	11/16/2023	09:00 AM	07:48 PM						10.80
59	11/17/2023	08:53 AM	02:25 PM						5.53
60	11/19/2023	06:59 AM	02:46 PM						7.78
61	11/20/2023	07:00 AM	06:06 PM						11.10
62	11/21/2023	06:59 AM	01:47 PM						6.80
63	11/22/2023	07:02 AM	11:25 AM						4.38
64	11/24/2023	07:01 AM	05:40 PM						10.65
65	11/26/2023	06:59 AM	02:30 PM						7.52
66	11/27/2023	06:59 AM	05:56 PM						10.95
67	11/28/2023	06:59 AM	10:52 AM						3.88
68	11/29/2023	06:57 AM	10:59 AM						4.03
69	11/30/2023	07:01 AM	04:45 PM						9.73
70	12/03/2023	08:55 AM	04:44 PM						7.82
71	12/04/2023	08:55 AM	06:49 PM						9.90
72	12/06/2023	08:54 AM	05:00 PM						8.10
73	12/07/2023	08:56 AM	05:01 PM						8.08
74	12/08/2023	08:57 AM	05:00 PM						8.05
75	12/13/2023	08:54 AM	05:00 PM						8.10
76	12/14/2023	08:55 AM	05:00 PM						8.08
77	12/15/2023	08:55 AM	05:00 PM						8.08
78	12/17/2023	08:59 AM	06:45 PM						9.77
79	12/18/2023	09:02 AM	05:01 PM						7.98
80	12/20/2023	08:57 AM	05:00 PM						8.05
81	12/21/2023	08:59 AM	05:00 PM						8.02
82	12/22/2023	09:00 AM	06:14 PM						9.23
83	12/24/2023	06:57 AM	01:40 PM						6.72
84	12/27/2023	08:56 AM	06:22 PM						9.43
85	12/28/2023	09:00 AM	05:00 PM						8.00
86	12/29/2023	08:59 AM	05:00 PM						8.02
87	12/31/2023	06:58 AM	03:00 PM						8.03
88	01/03/2024	08:54 AM	05:45 PM						8.85
89	01/04/2024	08:55 AM	05:00 PM						8.08
90	01/05/2024	08:58 AM	05:53 PM						8.92
91	01/06/2024	06:58 AM	11:52 AM						4.90
92	01/07/2024	08:57 AM	05:00 PM						8.05
93	01/08/2024	08:56 AM	06:00 PM						9.07
94	01/10/2024	09:01 AM	04:32 PM						7.52
95	01/11/2024	08:59 AM	04:46 PM						7.78
96	01/12/2024	09:01 AM	04:45 PM						7.73
97	01/14/2024	08:58 AM	06:00 PM						9.03
98	01/15/2024	08:59 AM	05:00 PM						8.02
99	01/17/2024	08:56 AM	04:15 PM						7.32
100	01/18/2024	09:14 AM	05:00 PM						7.77
101	01/19/2024	09:01 AM	07:00 PM						9.98
102	01/21/2024	08:56 AM	05:51 PM						8.92
103	01/22/2024	09:00 AM	05:00 PM						8.00
104	01/24/2024	08:58 AM	04:15 PM						7.28
105	01/25/2024	08:57 AM	05:00 PM						8.05
106	01/26/2024	09:00 AM	05:01 PM						8.02
107	01/27/2024	06:54 AM	11:01 AM						4.12
108	09/06/2023	08:00 AM	03:50 PM						7.83
109	09/07/2023	07:52 AM	02:50 PM						6.97
110	09/08/2023	09:56 AM	06:00 PM						8.07
111	09/11/2023	09:02 AM	05:00 PM						7.97
Sheet1

Cell Formulas
Range	Formula
D1	D1	=INDEX(SORT(A13:A200),30)

Note that data doesn't actually go to row 200, I just put that as a comfortable range because the last row of data is variable for everyone. Thanks for the test Here link. I hadn't known about that.

 

[Fluff]

Thanks for that
You have 4 rows of dates at the end which are earlier than 19th Oct hence you get the 19th not 23rd

 

[bh24524]

Thanks for that
You have 4 rows of dates at the end which are earlier than 19th Oct hence you get the 19th not 23rd

The formula should yield October 19th? It's actually September 19th that it is giving though, that's what's puzzling me.

 

[Fluff]

In that case your dates are probably text & not real dates. What does

=isnumber(a14)

return?

 

[bh24524]

In that case your dates are probably text & not real dates. What does

=isnumber(a14)

return?

It is coming up with False. It is sounding like it might be quicker to just manually sort the data and then type the original index formula, no?

 

[Fluff]

In that case they are text & not dates. It would be best to convert them to proper dates, which you can do using text to columns on the data tab.

 

[bh24524]

I'll use your original formula as the solution for this as it does work when the data is sorted. I'm just trying to come up with the quickest and easiest method to doing this and there will be multiple people doing it at times, so I kinda just want to get a best practice established that's easy for them to remember. From what I've observed, most people here don't seem to be familiar with the Text To Columns option - I use it all the time but am in the minority - So I think just having them sort it and then either highlight the cells until it reaches the needed count or using the original formula might be easiest. Thanks for your help. Sorry we ended up having to spend all this time on this only to figure out it was that