## Find date range

*Article updated on November 21, 2017*

Need help with a formula Please.

I need to figure out the date range of a cell. So if cell "E2" has a date of 11/23/2012 then that would need to fall under date range of "11/19 - 11/25".

I have the following date range I need to identify: "11/12 - 11/18", "11/19 - 11/25", "11/26 - 12/2", "12/3 - 12/9", "12/10 - 12/16", "12/17 - 12/23", "12/24 - 1/1/2013".

I tried the formula below but I keep getting a result of "False". I appreciate any help I can get. Thank you

**Answer:**

**Array formula in cell B2:**

Recommended article

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

**How to create an array formula**

- Select cell B2
- Click in formula bar
- Copy (Ctrl + c) and paste above formula (Ctrl + v) to formula bar
- Press and hold Ctrl + Shit
- Press Enter

### Explaining array formula in cell B2

**Step 1 - Find matching date range**

IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8),1,0)

returns

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

**Step 2 - Find row**

MATCH(1, IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8), 1, 0), 0)

becomes

MATCH(1, {0;1;0;0;0;0;0}, 0)

and returns 2.

**Step 3 - Return date**

INDEX($D$2:$D$8, MATCH(1, IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8), 1, 0), 0))

becomes

INDEX({41225; 41232; 41239; 41246; 41253; 41260; 41267}, 2)

and returns 41232.

**Step 4 - Convert value to date format**

TEXT(INDEX($D$2:$D$8, MATCH(1, IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8), 1, 0), 0)), "MM/DD")

becomes

TEXT(41232, "MM/DD")

and returns 11/19.

### Download excel *.xlsx file

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 […]### 5 Responses to “Find date range”

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

This formula should work for any date...

=TEXT(A1-WEEKDAY(A1,3),"mm/dd-")&TEXT(A1-WEEKDAY(A1,3)+6,"mm/dd")

Rick Rothstein (MVP - Excel),

Thanks!

Hi Oscar, found your website. I am complete Excel novice, and have been asked to setup a 'Calendar type' list which identifies alternate wks of the year as 'wk1 & wk2' for work planning purposes. The idea I think will be that this 'calendar list' will be a link/or dropdown to a planning tool, for staff to check whether they are in a wk1 or wk2! No idea where to start really, can you make any suggestions, simple pse as only used to filling in spreadsheet for work not actually setting them up!

Any suggestions gratefully received.

Karen.

@Karen,

Some questions...

1) What day of the week does your wk1 or wk2 start on (Mon, Tue, etc.)?

2) Show us an actual date that would be identified as being in wk1 (need that to orient your work calendar to the real calendar).

3) Are you seeking a VBA or Excel Formula solution?

3)

I need help to convert the list of dates into date range based on a criteria for a grouping by the employee id for example:

Data is as below:

EMPLOYEE ID Dates

24900002 2/27/2017

24900002 3/20/2017

24900005 3/3/2017

24900011 3/13/2017

24900014 3/9/2017

24900022 3/13/2017

24900023 2/25/2017

24900024 2/21/2017

24900024 2/22/2017

24900024 2/23/2017

24900024 2/24/2017

24900024 2/27/2017

24900024 2/28/2017

outcome required is as below:

Start Date End Date

24900002 2/27/2017 2/27/2017

24900002 3/20/2017 3/20/2017

24900005 3/3/2017 3/3/2017

24900011 3/13/2017 3/13/2017

24900014 3/9/2017 3/9/2017

24900022 3/13/2017 3/13/2017

24900023 2/25/2017 2/25/2017

24900024 2/21/2017 2/24/2017

24900024 2/27/2017 2/28/2017