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.
Identifying 1st, 2nd, etc
-
- Administrator
- Posts: 78448
- 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.
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 93
- 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?
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.
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Identifying 1st, 2nd, etc
How about in D2:
=COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,"<="&$C3)
Fill down.
=COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,"<="&$C3)
Fill down.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 93
- Joined: 10 Oct 2019, 09:43
Re: Identifying 1st, 2nd, etc
Wow!
Would never have considered using COUNTIFS.
Perfect - thank you.
Would never have considered using COUNTIFS.
Perfect - thank you.