Deeks asks:

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:

=IF(COLUMN(A1)=1, TEXT(A2, "M/D/YYYY")&" - "&TEXT(SMALL(IF(TEXT($A2+ROW(OFFSET($A1, 0, 0, $B2-$A2))-1, "DDD")="Mo", $A2+ROW(OFFSET($A1, 0, 0, $B2-$A2))-1, "A"), 1)-1, "M/D/YYYY"), TEXT(SMALL(IF(TEXT($A2+ROW(OFFSET($A1, 0, 0, $B2-$A2)), "DDD")="Mo", $A2+ROW(OFFSET($A1, 0, 0, $B2-$A2)), "A"), COLUMN(A1)-1), "M/D/YYYY")&" - "&IFERROR(TEXT(SMALL(IF(TEXT($A2+ROW(OFFSET($A1, 0, 0, $B2-$A2)), "DDD")="Mo", $A2+ROW(OFFSET($A1, 0, 0, $B2-$A2)), "A"), COLUMN(A1))-1, "M/D/YYYY"), TEXT($B2, "M/D/YYYY")))

How to create an array formula

  1. Select cell C2
  2. Copy above array formula
  3. Paste into formula bar
  4. Press and hold Ctrl + Shift
  5. Press Enter once
  6. Release all keys

How to copy array formula

  1. Select cell C2
  2. Copy (Ctrl + c)
  3. Select cell range C3:AH3
  4. Paste (Ctrl + v)