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.
Download Excel *.xlsx file
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
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download [โฆ]
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 [โฆ]
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 [โฆ]
Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, [โฆ]
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 [โฆ]
Identify rows of overlapping records
cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand [โฆ]
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 [โฆ]
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.
English
Spanish
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