Question: I am trying to create an excel spreadsheet that has a date range.

Example: Cell A1 1/4/09-1/10/09
Cell B1 1/11/09-1/17/09
Cell C1 1/18/09-1/24/09

How do I create a formula to do this?

Answer: See row 3 and formula below picture.

weekly date range

Formula in A3:

=TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7+6, "mm/dd/yy") + ENTER copied right as far as necessary.

Formula in A6:

=TEXT(DATE(2009,1,4)+(2-COLUMN(A:A)-1)*7,"mm/dd/yy")&"-"&TEXT(DATE(2009,1,4)+(2-COLUMN(A:A)-1)*7+6,"mm/dd/yy") + ENTER copied right as far as necessary.

Formula in A9:

=TEXT(DATE(2009, 1, 4)+(ROW(1:1)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2009, 1, 4)+(ROW(1:1)-1)*7+6, "mm/dd/yy") + ENTER copied down as far as necessary.

Formula in A18:

=TEXT(DATE(2009, 1, 4)+(2-ROW(1:1)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2009, 1, 4)+(2-ROW(1:1)-1)*7+6, "mm/dd/yy") + ENTER copied down as far as necessary.

How to use formulas in your workbook

Copy the formula to your sheet. To change start date, change bolded values in the above formula.

How the formula in cell A3 works

The goal is to create a date range from sunday to saturday.

When cell A3 is copied and pasted to the right, the date range adjusts.

The formula consists of two parts. Start date and end date of the date range. I have bolded the start date part of the formula below.

=TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7, "mm/dd/yy")&"-"&TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7+6, "mm/dd/yy")

Step 1 - First part of the formula creates start date

I have bolded the start date in this date range: 01/04/09 - 01/10/09.

TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7, "mm/dd/yy")

DATE(2009, 1, 4) is the start date of the date range series.

DATE(2009, 1, 4) returns 39817

(COLUMN(A:A)-1)*7 creates a number determined by the current column.

COLUMNS($A:A) returns 1.

COLUMNS($A:A) - 1 returns 0.

(COLUMNS($A:A)-1)*7 returns 0.

This number creates how many days there is between start dates in the date ranges.

Start dates are bolded below.

Example

01/04/09 - 01/10/09 ; 01/11/09 - 01/17/09 ; 01/18/09 - 01/10/09

11 - 4 = 7, 18 - 11 = 7.

When the formula is copied and pasted to the right, the relative reference in COLUMNS($A:A) changes.

This cell reference changes as the formula is copied and pasted to the right.

Example,

COLUMNS($A:A) is 1

COLUMNS($A:B) is 2

COLUMNS($A:C) is 3

TEXT(DATE(2009, 1, 4)+0, "mm/dd/yy")

becomes

TEXT(39817 + 0, "mm/dd/yy").

"M/DD/YY" is the specified number format.

TEXT(39817 + 0, "mm/dd/yy") returns 01/04/09

Step 2 - Second part of the formula creates the end date

The second part of the formula is

TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7+6, "mm/dd/yy")

The only difference between the first part of the formula:

TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7, "mm/dd/yy")

and the second part of the formula:

TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)-1)*7+6, "mm/dd/yy")

is +6 (bolded)

Example

01/04/09 - 01/10/09

10 - 4 = 6

Functions used in this blog post:

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code

TEXT(value, format_text)
Converts a value to text in a specific number format

ROW(reference) returns the rownumber of a reference

Related articles: