Create a list of dates with blanks between quarters
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
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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 […]
Find latest date based on a condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
Formula for matching a date within a date range
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 […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
List all unique events in a month
Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]
Lookup min max values within a date range
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
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.
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