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