## How to sort cells filtered by two dates

**Question:** I have a list containg dates and values.

How do I sort values between two specific dates?

**Answer:** Yellow cells are input cells. The gray area is the sorted value list.

Array formula in G4:G17:

Array formula in G4:G17:

**Array formula in F4:**

Copy this formula down to F17.

### Alternative array formula **Excel 2007**.

**Array formula in F4:**

Copy cell F4 and paste it down as far as needed.

**Array formula in G4:**

Copy cell G4 and paste it down as far as needed.

### Alternative array formula **Excel 2003**.

**Array formula in F4:**

Copy cell F4 and paste it down as far as needed.

**Array formula in G4:**

Copy cell G4 and paste it down as far as needed.

### Download excel sample file for this tutorial.

sort-cells-between-two-dates

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**VALUE(**text**)** converts a text string that represents a number to a number

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]You can click on headers to sort table for easy finding.

### 12 Responses to “How to sort cells filtered by two dates”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Verry good article...

One question...how do i extend this sorted list for more cells down?

I`ve already copied the formulas acordling to cells adresses but after row 17 nothing is shown despite the formulas entered there...

Thanks for answer...

@Sorin,

Try changing **all** the 17's in the formulas placed in the first row to the row number for your last piece of data and then copy that down.

Hy,

I`ve already do that...doesn`t work.

Thanks for promptitude..!!

You know other tricks..??

Sorin,

Array formula in G4:G17:

=IF(COUNT(SMALL(IF(($A$4:$A$22>D4), VALUE((B4:B22)), ""), ROW()- D4), VALUE((B4:B22)), ""), ROW()-

3))>=ROW()-3, SMALL(IF(($A$4:$A$223), "")If your lists doesn´t start on row 3 you also need to change 3's, bolded above.

Here is an alternative array formula

Excel 2007.Array formula in F4:=SMALL(IF(G4=$B$4:$B$17, $A$4:$A$17, ""), COUNTIF($F$3:F3, G4)+1) + CTRL + SHIFT + ENTER.

Copy cell F4 and paste it down as far as needed.

Array formula in G4:=SMALL(IF(COUNTIFS($E$4, ">="&$A$4:$A$17, $D$4, "<="&$A$4:$A$17), $B$4:$B$17, ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell G4 and paste it down as far as needed.

Here is an alternative array formula

Excel 2003.Array formula in F4:=SMALL(IF(G4=$B$4:$B$17, $A$4:$A$17, ""), COUNTIF($F$3:F3, G4)+1) + CTRL + SHIFT + ENTER. Copy cell F4 and paste it down as far as needed.

Array formula in G4:=SMALL(IF(COUNTIF($E$4, ">="&$A$4:$A$17)*COUNTIF($D$4, "<="&$A$4:$A$17), $B$4:$B$17, ""), ROW(A1)) + CTRL + SHIFT + ENTER. Copy cell G4 and paste it down as far as needed.

hello,

For the last of your post the sorted list outputs the same result for dates pov also for values. Example 1/10/2010 10 all the way down until reaches 14 rows...then stops..no more values..

Sorin,

Select and delete old array formulas in cell range F4:F17 and G4:G17.

Doubleclick cell F4

Copy and paste: =SMALL(IF(G4=$B$4:$B$17, $A$4:$A$17, ""), COUNTIF($F$3:F3, G4)+1)

Press CTRL + SHIFT + ENTER

Copy cell F4 and paste it down as far as needed.

Doubleclick cell G4

Copy and paste: =SMALL(IF(COUNTIF($E$4, ">="&$A$4:$A$17)*COUNTIF($D$4, "<="&$A$4:$A$17), $B$4:$B$17, ""), ROW(A1)) Press CTRL + SHIFT + ENTER Copy cell G4 and paste it down as far as needed.

With few modification into formula (I just replace some separators like ";" with "," and of course changing array, everything works just fine.

Thank you...

Sorin,

I am happy you worked it out!

Sorry about the ";" confusion.

What if i had duplicates in colum C and/or B and i wanted the latest date in colum A for the duplicate to be displayed that corrisponded with this duplicate

craig,

Array formula in cell E9:

Download excel *.xlsx filecraig.xlsx