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.

Recommended article

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Comments(48) Filed in category: Dates, Excel

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.

Recommended article

Convert date ranges into dates in excel

In a previous post I created a formula to convert dates into date ranges. Now it is time to create […]

Comments(5) Filed in category: Dates, Excel

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")

Recommended article

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Comments(83) Filed in category: Dates, Excel

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

Visualize date ranges in a calendar

Here is a picture of a simple calendar. I have used conditional formatting to: highlight date ranges (green) highlight possible […]

Comments(10) Filed in category: Calendar, Excel, Overlapping

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: