How do I Rank Dates?

[Lou]

Hello:

Column A - Has various dates from 2003
Column B - I would like to put a formula that gives it a
rank from 1 to 12 , 1 being earliest thru 12 being
latest..?

Any ideas would be great.
Thank you.
Lou

 

[immanuel]

If your date range is A1:A20, Use:

=SMALL(A1:A20,1)

for the earliest date in A1:A10. Use:

=SMALL(A1:A20,12)

for the 12th earliest date.

A quick way to populate B1:B12 with the 12 earliest dates:

In B1:

=SMALL(A1:A20,ROW())

And drag down to B12.

Note: You may need to format your cells in Column B as dates.

/i.

 

[Chip Pearson]

Lou,

Dates are just numbers, so you can use the RANK function to do this. E.g.,

=RANK(A1,A$1:A$10,1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[David]

Lou,
Assuming A2 is the first cell in the range of dates, in B2
type:
=Rank(A2,$A:$A,1) Then copy & paste down the sheet. (will
return #N/A for blank cells
David

 

[immanuel]

Wow. Sorry about that. Totally misread the question!

/i.

 

[Guest]

Thank you David works like a charm.

Lou

-----Original Message-----
Lou,
Assuming A2 is the first cell in the range of dates, in B2
type:
=Rank(A2,$A:$A,1) Then copy & paste down the sheet. (will
return #N/A for blank cells
David
.

 

[Lou]

Now I need to do this in access 2002... Any ideas..?

Thanks
Lou