## Date ranges for 7 days in excel

I need a formula that if I enter a start date in field B1 such as 6/8/11 it will give me the date ranges for 7 days in fields B3-B14. Does that make sense?

Basically I want a formula that will tell me when a client is admitted to services on 6/8/11, their week 1 is 6/8/11 to 6/15/11; week 2 is 6/16/11-6/22/11 etc through 12 weeks.

I want the initial date in B1 to be the only value that I have to change to produce these results. Is that possible?

**Answer:**

I could create a big formula in cell range B3:B14 but instead I going to simplify formulas and use three columns. Then people can more easily understand and customize the formulas.

The formulas are dynamic meaning when a date is entered in cell B1 cell range B3:D14 is instantly recalculated.

**Formula in cell B3:**

**Formula in cell B4:**

Copy (Ctrl + c) cell B4 and paste (Ctrl + v) to cell range B5:B14.

**Formula in cell C3:**

Copy (Ctrl + c) cell C3 and paste (Ctrl + v) to cell range C4:C14.

**Formula in cell D3:**

Copy (Ctrl + c) cell D3 and paste (Ctrl + v) to cell range D4:D14.

### Download excel example file

date ranges 7 days.xls

(Excel 97-2003 Workbook *.xls)

### Explaining formula in cell C3

=$B$1+ROW(A1)*7

**Step 1 - Create an absolute cell reference to start date**

=**$B$1**+ROW(A1)*7

$B$1 is an absolute cell reference. To create a reference to another cell, double click cell C3. Type = and left click cell B1. The formula becomes =B1.

Let´s convert the cell reference to an absolute cell reference. Absolute cell references are cell references that doesn´t adjust and change when copied. Press F4.

The formula becomes: =$B$1.

**Step 2 - Make dates with interval**

=$B$1**+ROW(A1)*7**

ROW(A1). A1 is a relative cell reference to cell A1. A relative cell reference is a cell reference that adjusts and change when copied. ROW(A1) returns the row number of a reference. ROW(A1) returns 1.

ROW(A1)*7 becomes

1*7

and returns 7.

**Step 3 - All together**

=$B$1+ROW(A1)*7** **

becomes

40702+1*7

and returns 40709. Formatted as a date, cell C3 returns 6/15/11.

Cell C4 becomes =$B$1+ROW(A2)*7

=40702+2*7

becomes

=40702+14

and returns 40716. Formatted as a date, cell C4 returns 6/22/11.

**Functions in this article:**

**ROW(**reference**)** returns the rownumber of a reference

**TEXT(**value, format_text**)**

Converts a value to text in a specific number format

### 4 Responses to “Date ranges for 7 days in excel”

### Leave a Reply

**How to add vba code to your comment:**

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

your 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

Thanks! This is very helpful!

You are welcome!

How do I create a function that will take the date range I enter and continue with it? for example, if I enter, "12/30/13-01/05/14" in cell a3, I want a4 to have, "01/06/14-01/12/14" and so on down the column.. Any and all help would be greatly appreciated!

Very helpful. I'm at a novice level on most of Office products but I seem to muddle through alright. This got me through so I could copy/paste the date range I wanted.

I was looking for the same thing Andrew (above) referred to but this worked wonderfully. Thanks!