Author: Oscar Cronquist Article last updated on November 19, 2018

Array formula in D5:

=SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1))

How to create an array formula

  1. Copy array formula
  2. Select cell D5
  3. Paste formula in formula bar
  4. Press and hold Ctrl + Shift
  5. Press Enter

How to copy array formula

  1. Select cell D5
  2. Copy (Ctrl + c)
  3. Select cell range D6:D15
  4. Paste (Ctrl + v)

Explaining formula in cell D5

Step 1 - Comparison end date

The less than sign and the equal sig allows you to check if values in cell range are less than or equal to the end date.

($B$3:$B$12<=$E$3)

becomes

{39836;39848;39850;39836;39844;39824;39845;39826;39851;39859}<=39859

and returns

{TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE}.

Step 2 - Comparison start date

The greater than sign and the equal sig allows you to check if values in cell range are greater than or equal to the start date.

($B$3:$B$12>=$E$2)

becomes

{39836;39848;39850;39836;39844;39824;39845;39826;39851;39859}>=39828

and returns

{TRUE;TRUE; TRUE; TRUE;TRUE;FALSE;TRUE; FALSE;TRUE; TRUE}

Step 3 - Multiply arrays

Both conditions must evaluate to TRUE.

($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2)

becomes

{TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE}*{TRUE;TRUE; TRUE; TRUE;TRUE;FALSE;TRUE; FALSE;TRUE; TRUE}

and returns

{1;1;1;1;1;0;1;0;1;1}

Boolean Boolean Multiply
FALSE FALSE 0
TRUE FALSE 0
TRUE TRUE 1

Step 4 - Replace TRUE with the corresponding date

The IF function replaces TRUE values with the corresponding dates and FALSE with "A".

IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A")

becomes

IF({1;1;1;1;1;0;1;0;1;1}, {39836;39848;39850;39836;39844;39824;39845;39826;39851;39859}, "A")

and returns

{39836; 39848; 39850; 39836; 39844; "A"; 39845; "A"; 39851; 39859}

Step 5 - Sort numbers (dates)

The SMALL function extracts the k-th smallest number and ignores text values like "A".

SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1))

becomes

SMALL({39836; 39848; 39850; 39836; 39844; "A"; 39845; "A"; 39851; 39859}, ROWS($A$1:A1))

The ROWS function counts rows in a cell reference, this cell reference $A$1:A1 expands as the formula is copied to cells below. This will extract a new value in each cell.

SMALL({39836; 39848; 39850; 39836; 39844; "A"; 39845; "A"; 39851; 39859}, ROWS($A$1:A1))

becomes

SMALL({39836; 39848; 39850; 39836; 39844; "A"; 39845; "A"; 39851; 39859}, 1)

and returns "1/23/2009" in cell D6.

Get Excel *.xlsx file

sort-dates-within-a-date-range-using-excel-array-formula.xlsx

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

How to identify two consecutive dates in a list

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

Filter unique values and sort based on adjacent date

The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]

Filter unique distinct values based on a date range

Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

Extract unique values from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

List dates outside specified date ranges

The Excel defined table contains start and end dates for each date range in cell range A3:B10. Cell B13 is […]

Advanced custom date filter

Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]