Thread: Date Sorting
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default Date Sorting

I put your date/times into column A, occupying A1 to A21. This formula
went into cell B1:

=INT(MIN(A1:A21))

and this finds the earliest date in the list. Then put this formula in
F1:

=MIN(IF(INT(A$1:A$21)=B$1+ROW()-1,A$1:A$21,100000))

This is an array formula, which means that you do not press ENTER in
the normal way once you have typed the formula in - instead, you have
to do CTRL-SHIFT-ENTER at the same time. If you do it correctly, Excel
will wrap curly braces { } around the formula (do not type these
yourself).

Then you can just copy the formula down column F for as many days as
you think there are in the data set. If you don't want to guess this,
then this formula in C1:

=INT(MAX(A1:A21))

and this in D1:

= C1 - B1 + 1 ,(formatted as number, 0dp)

will tell you how many days you have.

Hope this helps.

Pete