## Identifying 1st, 2nd, etc

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

### Identifying 1st, 2nd, etc

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.

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

### Re: Identifying 1st, 2nd, etc

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

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.

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

### Re: Identifying 1st, 2nd, etc

=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

Wow!

Would never have considered using COUNTIFS.

Perfect - thank you.