## Create a quartely date range

I will demonstrate three different methods to build quarterly date ranges in this article.

The two first methods have a date in a cell each where the third method has two dates in a cell.

### Create a quarterly date range using a clever built-in feature in Excel

Excel lets you copy formulas and data using the fill handler, a handy tool that automates your work. It is also smart enough to create number sequences.

Conveniently Excel dates are in fact numbers so the fill handler works fine with dates, as well.

Type the start and end date of the first quarterly date range in row 3. Type the second date range in row 4, see picture below.

Select cell range B3:C4. Click and hold on the black dot.

Drag to cells below as far as needed.

Excel automatically creates the following date ranges using the selected cells as a guide to determine the range and starting point.

### Quarterly date ranges using a formula

You also have the option to build a date range using formulas. I have entered 1/1/2017 in cell B3 and the following formula in cell C3:

See picture below. The formula adds 3 months to the date in cell B3 and then subtracts 1 to get the last date for the first quarterly date range. This setup takes into account that months have 30 or 31 days.

Type this formula in cell B4:

It is almost identical to the formula in cell C3. Then copy cell C3 and paste to cell C4.

Now select cell range B4:C4 and click and hold on black dot, see picture above.

Drag to cells below as far as needed. This action copies the formulas in cell range B4:C4 and pastes them to cells below.

### Date ranges in one cell each

The following picture shows date ranges in a cell each, to achieve that we need to build a somewhat more complicated formula. The formulas in cell A4 and A7 must be copied to cells to the right of the start cell.

### Array formula in A4:

copied right as far as necessary.

### Array formula in A7:

copied right as far as necessary.

### Array formula in A10:

copied down as far as necessary.

### Array formula in A17:

copied down as far as necessary.

### Functions in this article

**ROW(**reference**)** returns the row number of a reference

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

**TEXT(**value, format_text**)**

Converts a value to text in a specific number format

**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

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

I will demonstrate three different techniques to build monthly date ranges in this article. Two of these techniques are easy because they […]

How to convert radians to fractions of pi

The formula in cell D3 converts the radian decimal value to fractions of pi. Formula in cell D3: =TEXT(C3/PI(),"?/?")&"*"&CHAR(182) Explaining […]

Excel function syntax TEXT(value, format_text) Converts a value to text in a specific number format. Arguments value - The string […]

### One Response to “Create a quartely date range”

### 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.

**Contact Oscar**

You can contact me through this contact form

How would I simply extrapolate a date range in ONE column BY QUARTER? I would like to start on any given day and drag the right side of the cell and it increase the date by Quarters...not days, weeks, months or years--Quarters.

Stunned at how difficult it has been to find the solution to this.

Your way above I suppose will allow me to accomplish what I want, but with extra steps/info I do not need.

I hope I've explained this clearly enough.

Why won't Excel simply put "Quarter" as an option in the date range extension already in the product?

I look forward to your wisdom.

Thanx

Cheers!