# Create a quartely date range

I will demonstrate three different methods to build quarterly date ranges in this article.

The two first methods have a date in a cell each where the third method has two dates in a cell.

### Table of Contents

## 1. Create a quarterly date range using a clever built-in feature in Excel

Excel lets you copy formulas and data using the fill handler, a handy tool that automates your work. It is also smart enough to create number sequences.

ConvenientlyÂ Excel dates are in fact numbers so the fill handler works fine with dates, as well.

Type the start and end date of the first quarterly date range in row 3. Type the second date range in row 4, see picture below.

Select cell range B3:C4. Press and hold on the black dot.

Drag to cells below as far as needed.

Excel automaticallyÂ creates the following date ranges using the selected cells as a guide to determine the range and starting point.

## 2. Quarterly date ranges using a formula

You also have the option to build a date range using formulas. I have entered 1/1/2017 in cell B3 and the following formula in cell C3:

See picture below. The formula adds 3 months to the date in cell B3 and then subtracts 1 to get the last date for the first quarterly date range. This setup takes into account that months have 30 or 31 days.

Type this formula in cell B4:

It is almost identical to the formula in cell C3. Then copy cell C3 and paste to cell C4.

Now select cell range B4:C4 and press and hold on black dot, see picture above.

Drag to cells below as far as needed. This action copies the formulas in cell range B4:C4 and pastes them to cells below.

## 3. Date ranges in one cell each

The following picture shows date ranges in a cell each, to achieve that we need to build a somewhat more complicated formula. The formulas in cell A4 and A7 must be copied to cells to the right of the start cell.

Array formula in A4:

copied right as far as necessary.

Array formula in A7:

copied right as far as necessary.

Array formula in A10:

copied down as far as necessary.

Array formula in A17:

copied down as far as necessary.

## 4. Create a list of dates with blanks between quarters

**Question:**How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4)

**Answer:**

Formula in B3:

If you don't need the formula to be dynamic, like in a dashboard or an interactive sheet then simply follow Jarek's instructionsÂ below.

**Jarek comments:**

*Create a list of 3 months in a quarter**Select it together with a blank cell beneath**Drag down*

*Thanks!*

### Explaining formula in cell B3

#### Step 1 - Check blank cells above

We want to know how many cells above have been populated with dates in order to calculate the next date.

$B$2:B2<>""

becomes

"Months"<> ""

and returns FALSE.

#### Step 2 - Sum values

The SUMPRODUCT function can't sum boolean values, we need to convert them into theirÂ numerical equivalents.

SUMPRODUCT(($B$2:B2<>"")*1)+1

becomes

SUMPRODUCT((FALSE)*1)+1

becomes

SUMPRODUCT(0)+1

becomes

0+1 and returns 1.

#### Step 3 - Create date

The DATE function creates an Excel date based on year, month and day.

DATE(2018, SUMPRODUCT(($B$2:B2<>"")*1)+1, 1)

becomes

DATE(2018, 1, 1)

and returnsÂ 43132.

#### Step 4 - Convert the Excel date to something we understand

The TEXT function can do many things, one of them is to format an Excel date.

TEXT(DATE(2018, SUMPRODUCT(($B$2:B2<>"")*1)+1, 1)-1, "M/D/YYYY")

becomes

TEXT(43132, "M/D/YYYY")

and returns 1/31/2018 in cell B3.

#### Step 5 - Create a blank between quarters

The IF function uses the logical expression to determine if a date or blank is to be returned.

IF(MOD(ROWS($A$1:A1), 4), TEXT(DATE(2018, SUMPRODUCT(($B$2:B2<>"")*1)+1, 1)-1, "M/D/YYYY"), "")

becomes

IF(MOD(ROWS($A$1:A1), 4), "1/31/2018", "")

The MOD function returns the remainder after a number is divided, this allows you to create a squency of values that repeats 1, 2, 3, 0, 1, 2,3, ... and so on.

IF(MOD(ROW(A1), 4), "1/31/2018", "")

becomes

IF(1, "1/31/2018", "")

and returnsÂ "1/31/2018" in cell B3.

### Get Excel *.xlsx file

Create-a-list-of-dates-with-blanks-between-quarters.xlsx

## 5. Create a monthly date range

I will demonstrate three different techniques to build monthly date ranges, in this section. Two of these techniques are easy because they have the start and end date in a cell each.

To have two dates in the same cell makes it more complicated but I have a solution for that, as well.

### 5.1 Create date ranges using a built-in feature

To build a date range that begins with the first date in a month and ends with the last date in a month follow these steps:

- Type the start date of your first date range in one cell
- Type the end date of your first date range in the next cell
- Repeat above steps to enter the second date range in cells below

- Select all cells
- Press and hold on black dot

- Drag to cells below as far as needed

Excel dates are actually numbers, the above technique uses Excel's ability to quickly create number sequences using the selected cells as a guide to determine the size of the date ranges below.

### 5.2 Basic formula

Type the start date in a cell, in this case, 1/1/2017. Type the following formula in the next cell:

The formula in cell C3 calculates the last date for the month and year in cell B3. It actually calculates the first date in the next month and then subtracts with 1.

This solves the issue with some months having 30 days and some having 31 and February with 28 days in common years and 29 days in leap years.

The next date range has the first date in the next month as the start date. The formula is almost identical to the previous formula.

Formula in cell B4:

Now copy cell C3 and paste to cell C4.

The relative cell references in the formula changes accordingly, you can read more about relative and absolute cell references here:

Recommended articles

What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]

Copy cell range B4:C4 and paste to cells below as far as needed.

### 5.3 Advanced formula

The following picture shows you date ranges in a single cell each. To get that you need to build a somewhat more complicated formula.

Formula in A4:

copied right as far as necessary. The TEXT function formats the number from the DATE function as a readable text string in a format you choose. You can read more about the TEXT function here:

Recommended articles

This article demonstrates how to use the TEXT function in great detail. The formula in cell D3 formats the number […]

Formula in A7:

copied right as far as necessary.

Formula in A10:

copied down as far as necessary.

Formula in A19:

copied down as far as necessary.

### 5.4 Get *.xlsx file

Create a monthly date range.xlsx

More than 1300 Excel formulas

### Excel categories

### 9 Responses to “Create a quartely date range”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

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

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

The question of exactly *why* you would want blank rows in your data remains unanswered.

I guess it would increase sheet readability in large lists.

I just try to solve peoples excel questions. If the solutions seem useful I post them here.

why not simply create a list of 3 months in a quarter

then select it together with a blank cell benneath

then drag down

?

Jarek,

your answer to this question is the easiest one. Thanks for your contribution!

welcome, any time

;-)

credit to my boss - I once tried to show him a similar formula solution. he listened and presented me a solution of his own (as above). I was devastated.

;-)

need to truncate the undermentioned column to create columns of weeks it has, for eg. 1/26/2011 was a wed, so need a column for 1/26/2011-sun 1/29/2011, and then onwards each week starting monday till 8/31/2011. please help asap

Assigned Dates

1/26/2011 - 8/31/2011

2/1/2011 - 3/30/2011

2/1/2011 - 3/30/2011

2/1/2011 - 3/30/2011

11/1/2010 - 2/11/2011

1/26/2011 - 8/31/2011

Deeks,

read this post: Filter weeks from a date range

Dear

How can I change the formula, inserting blanks between each group :

(no macro please, shared file, users forget to enable macro's)

link to image :

https://s24.postimg.org/smz0khqc5/Blank_between_groups.jpg

Thanks for your help

Oliver

How would I simply extrapolate a date range in ONE column BY QUARTER? I would like to start on any given day and drag the right side of the cell and it increase the date by Quarters...not days, weeks, months or years--Quarters.

Stunned at how difficult it has been to find the solution to this.

Your way above I suppose will allow me to accomplish what I want, but with extra steps/info I do not need.

I hope I've explained this clearly enough.

Why won't Excel simply put "Quarter" as an option in the date range extension already in the product?

I look forward to your wisdom.

Thanx

Cheers!