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

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

How do I create a formula to do this?

What are dates in Excel?

First, what are dates in Excel? They are actually numbers, try this:

  1. Type a date in a cell
  2. Select the cell
  3. Press CTRL + 1 to open the "Format Cells" dialog box
  4. Select "General"
  5. Click OK button
  6. The cell you selected now has a different formatting. This shows you what dates are in Excel.

Date 11/15/2017 is 43054. Excel starts numbering dates at 1/1/1900 with value 1. Type 1 in a cell and change the cell formatting to "Date" and see what Excel displays.

Date 11/15/2017 is 43053 days from 1/1/1900.

Basic date ranges

You can build a formula or use a built-in feature to build date ranges, read on to learn more.

Addition

Now you know that dates in Excel are numbers. You can easily create a date range by adding a number to a date.

The picture below shows a start date 11/15/2017, adding number 7 to that date returns 11/22/2017

This allows you to quickly build date ranges simply by adding a number to a date.

Now select cell B4 and type =C3+1

Copy cell C3 and paste to cell C4.

Relative cell references changes when you copy a cell and paste it to a new cell and I am going to use that now.

Copy cell range B4:C4 and paste it to cells below.

You have now built multiple date ranges using simple mathematics.

Create a date sequence

Excel has a great built-in feature that allows you to create number sequences in no time. Since dates are numbers in Excel you can use the same technique to build date ranges.

To build date ranges that have the same range but dates change, follow these steps:

  1. Type the start date and the end date in a cell each
  2. Type the second start date an end date in cells below
  3. Select both date ranges
  4. Click and hold on black dot
  5. Drag to cells below
  6. Release mouse button

Excel has now built date ranges automatically using the two first date ranges as a template.

Did you know that it is possible to check if date ranges overlap? Read the following article

Find overlapping date ranges in excel

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]

Comments(24) Filed in category: Excel, Overlapping

or check out the category showing all articles about overlapping date ranges.

Advanced formula

If you need to build date ranges that have both the start and end date in the same cell you need to build a more complicated formula. The TEXT function lets you format dates out of numbers.

The DATE function creates the number using three arguments. The first argument is the year, the second argument is the month and the third argument is the day.

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

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 latest date

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

Comments(85) Filed in category: Dates, Excel

Step 1 - First part of the formula creates the start date (bolded)

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

Download excel *.xlsx file

create date ranges.xlsx