## 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 extracted dates sorted?

**Answer: **

**Array formula in D5:**

The following article demonstrates how to highlight dates in a date range:

**Highlight dates within a date range using conditional formatting**

Comments(15) Filed in category: Conditional formatting, Excel

**How to create an array formula**

- Copy array formula
- Select cell D5
- Paste formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Count values, Excel

**How to copy array formula**

- Select cell D5
- Copy (Ctrl + c)
- Select cell range D6:D15
- Paste (Ctrl + v)

Recommended article:

**Filter weeks from a date range**

Comments(2) Filed in category: Dates, Excel

### Named ranges

List (A1:A10)

List_start (A1)

sorted_list_start (D5)

start_date (E1)

end_date (E2)

Recommended article:

**Create a dynamic named range in excel**

Comments(12) Filed in category: Excel, Named range

### Download excel example file

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

(Excel 97-2003 Workbook *.xls)

**Functions used in this blog post**

**SMALL function and LARGE function**

Comments(12) Filed in category: Excel

Comments(9) Filed in category: Excel

### Category: Sort values

Comments(81) 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

Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]Comments(13) Filed in category: Excel, Sort values

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

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

Comments(7) Filed in category: Excel, Mod, Sort values

Sort values in a cell using a custom delimiter (vba)

The following macro let´s you select a cell range and a delimiting character. The macro sorts the values in each […]Comments(5) Filed in category: Excel, Sort values

### 23 Responses to “Sort dates within a date range using excel array formula”

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

I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09

Cell B1 1/11/09-1/17/09

Cell C1 1/18/09-1/24/09

How do I create a formula to do this?

See this blog post: https://www.get-digital-help.com/2009/06/16/create-a-date-range-using-excel-formula/

I have a very similar problem with dates but my dates are in a matrix (an array). So columns A through J (A2:J60) have dates and I want to put the entire range of dates into one column say column A in sheet 2 but I want them in ascending order. Any chance you can help with that.

Kind Thanks,

Eddie

Eddie G,

Array formula in cell A1, sheet2:

=SMALL(Sheet1!$A$2:$J$60, ROW(A1))

Thanks for bringing this post to my attention. I have now simplified the formula in this post.

I'm using a formula to get a report that bring me the total persons from a specific colum in a determinated date:

{SUM(IF((DATE>=FirstDate)*(DATE<=LastDate)*(TYPE=TypeName)*(SUBTYPE=SubtypeName),NameColum2,0))}

When I want to get the result from the "Name Colum2" it works well, but when I change the colum ("NameColum3") it appears an #NAME? error.

I have an excel spreadsheet that has different colums:

TYPE,SUBTYPE,DATE,NameColum2,NameColum3,etc.

Could you help me?

Thanks

Ed

Ed,

You have probably misspelled the column name.

https://www.exceldigest.com/myblog/2009/03/02/how-to-correct-or-clear-a-name-error-in-a-formula/

Thank you very much! Now it is working very well.

Hi I am trying to apply this formula to a search I need to run. I have 4 types of searches, in a dropdown box with conditional formatting showing the user where to enter the specific data that needs to be searched for. I have been able to complete two the search if statements (bill number and file number) however I can not complete the other two which is invoice number and date range.

I would like the formula to always return the applicable bill num if the start and end date of the invoice falls in the specified range .

the formula is getting so long that I am lost. here is the link ...

https://www.dropbox.com/s/5o3n2kln2u8v7yn/invoice%20for%20lily%20%28Autosaved%29.xlsx

many thanks in advance ...

Juliana,

Download *.xlsx file

invoice-for-lily.xlsx

Hi Pro, i want to find day if given date and week, for example : if given Tuesday, 32th week, 2015 >>> how to use function to point out 4/8/2015 ?

Pls help me!

La Thăng,

Read this:

https://www.get-digital-help.com/2015/08/06/find-date-given-day-and-week/

[…] La Thăng asks: […]

Thanks so much Pro :) if you have chance to VietNam visit, pls tell me know ^^

If I want to search a certain criteria in two dates in above example. For an example, machine A within two dates.How I do it?

Pls help

i have A1 to A300 employee names

B1 to B300 start date of vacation

C1 to C300 end date of vacation

need to count how many employees are absent from work for each day in a year to create a chart (ex: 05.05.2016. 55 are absent, 06.05.2016. 59 are absent... and so on)

and, if its possible to make list of their names... but that is a separate question.

Hi Oscar

Really love your website. Its very useful. I'm using this formula but need help. I have a table with Dates, Codes, Description etc. These table is used to input data which may or may not be in date order.

I then need to copy this data into another area but I want it sorted by date order, which I have successfully done. However, I also want the Codes, Description etc linked to the date to also come across to the sorted date formula used above.

Is this possible?

Hi,

What I have to take on the place of list in this formula??

Pls Help

I am getting Num! Error in this Date Sorting Formula

Did you enter it as an array formula?

I have used in this way

=SMALL(IF((K5:K24=$L$1),K5:K24,""),K5:K24)

Lokesh

You need to use absolute cell refs except the last one, like this:

=SMALL(IF(($A$1:$A$10< =$E$2)*($A$1:$A$10>=$E$1), List, ""), ROW(A1))

Row(A1) returns a number that changes when you copy/paste the formula to cells below. That way you can get the smallest value and then the second smallest value and so on.

Hello Sir,

I have Got the Answer but I didn't understand the presence of Row(A1)in this Formula. Could u pls help me understand that