## Find date range

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

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

### 4 Responses to “Find date range”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your 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)