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.

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

  • Share/Bookmark

Related posts:

  1. Create a custom date range in excel
  2. Create a quartely date range in excel
  3. Extract dates using a drop down list in excel
  4. Create a monthly date range in excel
  5. Create a unique distinct list of a long list without sacrificing performance using vba in excel
  6. Create a date range using excel formula
  7. Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
  8. Extract dates and adjacent value in a range using a date critera in excel
  9. Determine the sum between a range of dates for a specific currency
  10. Create a dynamic stock chart using a web query and a drop down list in excel