## Date ranges: Weeks within a month

*Article updated on May 02, 2012*

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

**How to create an array formula**

- Select cell B1
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula in cell B1**

- Select cell B1
- Copy (Ctrl +c)
- Select cell range B2:B11
- Paste (Ctrl + v)

**Formula in cell A2:**

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

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 […]### 4 Responses to “Date ranges: Weeks within a month”

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

I have just found your site and had a very quick look.

It is AMAZING - FANTASTIC

So much information under one heading I don't know where to start.

Thank you very very much, I am sure that this will improve my skills.

Regards Graham Ward

Graham Ward,

Thank you for commenting! I am really happy you like it!

Hi, Oscar

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

cell B1:

=IF(MONTH(A1)<>MONTH(A1+6), DATE(YEAR(A1),MONTH(A1)+1,1)-1, MIN(A1+6, A1-WEEKDAY(A1)+7))

aMareis

Your formula works! Thank you for your contribution.