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

Answer:

Create a list of dates with blanks between quarters

Formula in A2:

=DATE(2009,9,30)

Array formula in A3:

=IF(A2="", DATE(YEAR(A1), MONTH(A1)+2, 1)-1, IF((MONTH(A2)/3)-ROUND((MONTH(A2)/3), 0)=0, "", DATE(YEAR(A2), MONTH(A2)+2, 1)-1))

copied down as far as necessary.

Jarek comments:

  1. Create a list of 3 months in a quarter
  2. Select it together with a blank cell benneath
  3. Drag down

Thanks!

Download excel sample file for this tutorial.

Create a list of dates with blanks between quarters.xls
(Excel 2007 Workbook *.xlsx)
Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROUND(number, num_digits)
Rounds a number to a specified number of digits

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code

MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999