Anees asks:

Hi,
The above formula in A9 "Increasing date in a column" works pretty good however I have a small request to change the formula as per my need.
As I fill the first column in excel with this formula than use + handler on bottom right of the column to drag and copy it to next cell so the date increases. That works but when it reaches the end of month it continues in the same cell. Is it possible so at the end of the month the range would stay within the month instead of increasing to next month?
Here let me try to explain visually.
01/01/12-01/07/12
01/08/12-01/14/12
01/15/12-01/21/12
01/22/12-01/28/12
01/29/12-02/04/12 And next month would be per week in each coulmn as well and so on.
02/01/12-02/04/12
02/05/12-02/11/12
02/12/12-02/18/12
02/19/12-02/25/12
02/26/12-02/29/12
03/01/12-03/03/12
Thank you.

Answer:

The array formula becomes quickly complicated if I try to concatenate the start and end date in one cell. I am going to use two cells.

Date in cell A1: 

2012-01-01

Array Formula in cell B1:

=IF(MONTH(A1)<>MONTH(A1+6), DATE(YEAR(A1), MONTH(A1)+1, 1)-1, A1+MAX(IF(WEEKDAY(A1+{0, 1, 2, 3, 4, 5, 6})=7, {0, 1, 2, 3, 4, 5, 6}, "")))

How to create an array formula

  1. Select cell B1
  2. Paste array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula in cell B1

  1. Select cell B1
  2. Copy (Ctrl +c)
  3. Select cell range B2:B11
  4. Paste (Ctrl + v)

Formula in cell A2:

=B1+1

Copy cell A2 and paste down as far as necessary.

Explaining array formula in cell B1

Step 1 - Compare dates and check if months are not equal

=IF(MONTH(A1)<>MONTH(A1+6), formula_TRUE, formula_FALSE)

becomes

=IF(MONTH(40909)<>MONTH(40909+6), formula_TRUE, formula_FALSE)

becomes

=IF(MONTH(40909)<>MONTH(40915), formula_TRUE, formula_FALSE)

becomes

=IF(1<>1, formula_TRUE, formula_FALSE)

Step 2 - Calculate days to last day in week

A1+MAX(IF(WEEKDAY(A1+{0, 1, 2, 3, 4, 5, 6})=7, {0, 1, 2, 3, 4, 5, 6}, ""))

becomes

40909+MAX(IF(WEEKDAY(40909+{0, 1, 2, 3, 4, 5, 6})=7, {0, 1, 2, 3, 4, 5, 6}, ""))

becomes

40909+MAX(IF(WEEKDAY({40909, 40910, 40911, 40912, 40913, 40914, 40915})=7, {0, 1, 2, 3, 4, 5, 6}, ""))

becomes

40909+MAX(IF({1, 2, 3, 4, 5, 6, 7}=7, {0, 1, 2, 3, 4, 5, 6}, ""))

becomes

40909+MAX({"", "", "", "", "", "", 6})

becomes

40909+6

and returns 40915 (2012-01-07) in cell A2.

Step 3 - Calculate last day in month

Example in cell B5

=IF(MONTH(A5)<>MONTH(A5+6), formula_TRUE, formula_FALSE)

becomes

=IF(MONTH(40937)<>MONTH(40943), formula_TRUE, formula_FALSE)

becomes

=IF(1<>2, formula_TRUE, formula_FALSE)

DATE(YEAR(A5), MONTH(A5)+1, 1)-1

becomes

DATE(YEAR(40937), MONTH(40937)+1, 1)-1

becomes

DATE(2012, 2, 1)-1

becomes

40940-1

and returns 40939 in cell B5.

Download excel *.xlsx file

Anees.xlsx