## 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

### Dates category

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

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

The image above demonstratesÂ an array formula in cell E4 that searches for the closest date in column A to the […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

This article demonstrates formulas that show if a date range is overlapping another date range.Â The second section shows how […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

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

This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]

This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 6 Responses to “Create a list of dates with blanks between quarters”

### 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.

;-)

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