## How to sort cells filtered by two dates

*Article last updated on February 02, 2018*

**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

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Sort dates within a date range

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]

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 […]

Converts a text string that represents a number to a number. This function is included for compatibility with other software. […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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