Author: Oscar Cronquist Article last updated on January 09, 2019

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/2009How 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.


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

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

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 […]

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

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

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

Find latest date based on a condition

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

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.


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.


COLUMNS($A:A) is 1

COLUMNS($A:B) is 2

COLUMNS($A:C) is 3

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


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)


01/04/09 - 01/10/09

10 - 4 = 6

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

Download Excel file

Enter your email to receive the workbook.
* You will also get a weekly newsletter, unsubscribe anytime!