## Filter weeks from a date range

*Article last updated on December 23, 2011*

need to truncate the undermentioned column to create columns of weeks it has, for eg. 1/26/2011 was a wed, so need a column for 1/26/2011-sun 1/29/2011, and then onwards each week starting monday till 8/31/2011. please help asap

Assigned Dates

1/26/2011 - 8/31/2011

2/1/2011 - 3/30/2011

2/1/2011 - 3/30/2011

2/1/2011 - 3/30/2011

11/1/2010 - 2/11/2011

1/26/2011 - 8/31/2011

**Answer:**

**Array Formula in cell C2:**

**How to create an array formula**

- Select cell C2
- Copy above array formula
- Paste into formula bar
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**How to copy array formula**

- Select cell C2
- Copy (Ctrl + c)
- Select cell range C3:AH3
- Paste (Ctrl + v)

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 […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

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 […]

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

Lookup two index columns in excel

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

### 2 Responses to “Filter weeks from a date range”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

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

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hi, Oscar

If B2=1-29-2011 then, your formula is not work.

Here's an alternative. It's not an array formula and shorter.

cell C2:

=IF($A2-WEEKDAY($A2,2)+7*COLUMN(A1)-$B2>=7,"", TEXT(MAX($A2,$A2-WEEKDAY($A2,2)+7*(COLUMN(A1)-1)+1), "m/d/yyyy") &" - "&TEXT(MIN($B2, $A2-WEEKDAY($A2,2)+7*COLUMN(A1)),"m/d/yyyy"))

aMareis,

I can´t find the file but thank you for commenting!