Excel: Remove rows containing specific dates

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Excel: Remove rows containing specific dates

← Previous post -

Filed in Excel on Nov.20, 2007. Email This article to a Friend

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.

Remove duplicates and sort dates by each row in excel

How to sort text cells filtered by two dates, part 2

Determine the sum between a range of dates for a specific currency

Compare two tables in excel: Remove common rows

Sort numeric values by closest to farthest to a specific number in excel (array formula)