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.
Table of Contents
1. 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. Press 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.
2. 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 press 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.
3. 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.
More than 1300 Excel formulasExcel categories
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.
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!