Scott,

Use this array formula, entered using Ctrl-Shift-Enter:

=RANK(A2,$A$2:$A$11,TRUE)-(COUNTIF($A$2:$A$11,"<"&A2)-SUM((1/COUNTIF($A$2:$A$11,$A$2:$A$11))*($A$2:$A$11<A2)))

Adjust the ranges to suit.

HTH,
Bernie
MS Excel MVP


"scottymelloty" <scottymelloty.22tdoc_1139240105.1126@excelforum-nospam.com> wrote in message
news:scottymelloty.22tdoc_1139240105.1126@excelforum-nospam.com...
>
> this is a sample of my data
>
> 15-01-06 xxxxx
> 15-01-06 xxxxx
> 15-01-06 xxxxx
> 20-01-06 xxxxx
> 20-01-06 xxxxx
> 20-01-06 xxxxx
> 27-01-06 xxxxx
> 27-01-06 xxxxx
>
> i want to rank the dates so i can do some working outs from them , when
> i rank the dates its starts off at no1 for all the 15-01-06 and then
> goes to no4 for the 20-01-06 then no 7 for the 27-01-06 data ,
>
> how can i rank them so it puts no1 for all 15-01 , then no2 for all
> 20-01 then no3 for 27-01 and so on
>
> there will be about 150 entries with the same date every week and i
> want it to count the whole group of dates as 1 rank instead of it
> counting every date basically
>
> or is there a better way , many thanks
>
>
> --
> scottymelloty
> ------------------------------------------------------------------------
> scottymelloty's Profile: http://www.excelforum.com/member.php...fo&userid=3808
> View this thread: http://www.excelforum.com/showthread...hreadid=508940
>