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

Question: how can i get the date range as : 01/01/09 - 01/15/09, 01/16/09 - 01/31/09 and so on I […]

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates. The array formula in this blog post counts for […]

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

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

How to remove numbers from a cell value

The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

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

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form