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

### Category: Dates

Create a date range using excel formula

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]Comments(142) Filed in category: Dates, Excel

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]Comments(83) Filed in category: Dates, Excel

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]Comments(48) Filed in category: Dates, Excel

Finding the nearest date in a range of dates using excel formula

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]Comments(42) Filed in category: Dates, Excel

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 […]Comments(31) Filed in category: Excel, Overlapping

### Category: Sort values

Comments(81) Filed in category: Excel, Sort values

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

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 […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]Comments(16) Filed in category: Excel, Sort values

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]Comments(13) Filed in category: Excel, Sort values, Vlookup

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