Author: Oscar Cronquist Article last updated on April 07, 2009

Question: How do I sort date ranges or intervalls by size in excel?

Answer: Here are the ranges sorted large to small.

Formula in E2:E11: =MATCH(LARGE(C2:C11-B2:B11, ROW()-1), C2:C11-B2:B11, 0) + Ctrl + Shift + Enter

Formula in F2:F11: =INDEX(\$B\$2:\$B\$11, MATCH(LARGE(C2:C11-B2:B11, ROW()-1), C2:C11-B2:B11, 0)) + Ctrl + Shift + Enter

Formula in G2:G11: =INDEX(\$C\$2:\$C\$11, MATCH(LARGE(C2:C11-B2:B11, ROW()-1), C2:C11-B2:B11, 0)) + Ctrl + Shift + Enter

Formula in H2:H11: LARGE(C2:C11-B2:B11, ROW()-1) + Ctrl + Shift + Enter

Download excel sample file for this article.
sorting-date-intervalls.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

LARGE(array,k) returns the k-th largest row number in this data set.

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])