Identifying 1st, 2nd, etc

Lost Paul
StarLounger
Posts: 56
Joined: 10 Oct 2019, 09:43

Identifying 1st, 2nd, etc

Post by Lost Paul »

So got collection of times.

What formula can I use to identify; which is earliest, which is the second earliest and so forth?

TIA

N.B. these are alongside a date column, so would need to meet the date criteria too.

User avatar
HansV
Administrator
Posts: 74155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Identifying 1st, 2nd, etc

Post by HansV »

Let's say the dates are in A2:A100 and the times in B2:B100.
For the earliest date/time, use

=SMALL($A$2:$A$100+$B$2:$B$100,1)

Format the cell with the formula as date + time.
For the second earliest, use

=SMALL($A$2:$A$100+$B$2:$B$100,2)

etc.
Regards,
Hans

Lost Paul
StarLounger
Posts: 56
Joined: 10 Oct 2019, 09:43

Re: Identifying 1st, 2nd, etc

Post by Lost Paul »

That goes part of the way.
I've attached what I was initially thinking, but I do like the use of SMALL.

How would I identify by each day?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 74155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Identifying 1st, 2nd, etc

Post by HansV »

How about in D2:

=COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,"<="&$C3)

Fill down.
Regards,
Hans

Lost Paul
StarLounger
Posts: 56
Joined: 10 Oct 2019, 09:43

Re: Identifying 1st, 2nd, etc

Post by Lost Paul »

Wow!

Would never have considered using COUNTIFS.

Perfect - thank you.