## Filter weeks from a date range

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

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 […]### 2 Responses to “Filter weeks from a 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

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!