## Excel: Remove rows containing specific dates

*Article updated on November 20, 2007*

**Problem: **How sort out all dates that don´t meet the criteria?

Criteria "Sort out dates later than 2006-11-18"

**Solution:**

Column A contains all data

Column B contains sorted data

C1 contains criteria date

Column B contains: =IFERROR(INDEX($A$1:$A$20, SMALL(IF($A$1:$A$20<$C$1, ROW($A$1:$A$20), ""), ROW(1:1))), "") + Ctrl + Shift + Enter.

Lets divide the formula for easy explanation:

IF($A$1:$A$20<$C, ROW($A$1:$A$20), "") checks the criteria, if met then the actual cell value row number is the result.

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

Index() uses the rownumber to "get" the sorted out values.

IFFERROR() removes the num# error.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

## Share this article